def GetMessagesByDay(server_id: int, date):
"""Gets the amount of messages from the given server in the give day """
messages = db.custom_retrieve(f"""SELECT Num FROM Messages
WHERE Date='{date}' AND Server={server_id}""")
if (len(messages) == 0):
return 0
return messages[0][0]
def GetLastMessages(server_id: int, amount: int = 10, start: int = 0):
"""Gets the amount of messages from the given server in the given interval """
messages = db.custom_retrieve(f"""
SELECT Date,Num FROM Messages WHERE Server={server_id}
ORDER BY Date DESC LIMIT {amount} OFFSET {start}
""")
return messages
async def AntiRecord(message):
"""Gets the days with the smallest amount of messages"""
data = db.custom_retrieve(
f"SELECT date,num FROM Messages WHERE server = {message.guild.id} ORDER BY num LIMIT 20 "
)
table = utils.genTableString(data, ["Data", "Mensagens"])
embed = utils.genEmbed(
"Tabela de Anti-Recordes",
table,
descp="Dias com os menores números \nde mensagens enviadas ")
await message.channel.send(embed=embed, content=None)
async def GetMessageEmpireWeek(message, client):
"""Gets the amount of messages in all the Empire in the week of the given day"""
r_command = message.content.split()
w_ago = 0
try:
w_ago = int(r_command[2])
except IndexError:
pass
if (w_ago) < 0:
await message.channel.send("Não há registros do futuro")
return
else:
date = datetime.now().date() - timedelta(7 * w_ago)
#Gets the raw data with the messages from the week of the given day
day_of_w = date.weekday()
monday = date - timedelta(day_of_w)
next_monday = monday + timedelta(days=7)
data = db.custom_retrieve(f""" SELECT Server,Num
FROM MESSAGES WHERE Date >= '{monday}' AND Date < '{next_monday}'
ORDER BY Num DESC""")
#Parses the raw data
messages = {}
for i in range(len(data)):
if (data[i][0] in messages.keys()):
messages[data[i][0]] += data[i][1]
else:
messages[data[i][0]] = data[i][1]
if len(data) == 0:
await message.channel.send(
f"Não há registro do ranking de {w_ago} semanas atrás")
return
table = utils.genTableServers(messages, client)
embed = utils.genEmbed(
"Ranking de Servidores",
table,
descp="Ranking de servidores por mensagens \nenviadas essa semana")
await message.channel.send(embed=embed, content=None)
async def Average(message):
"""Gets the daily messages average from the given amount of days ago"""
d_ago = 7
try:
r_command = message.content.split()
d_ago = int(r_command[2])
if (d_ago < 1):
await message.channel.send(
"Não é possível calcular a média de dias futuros")
return
except:
pass
avg = db.custom_retrieve(f"""
SELECT ROUND(AVG(num))::INTEGER FROM
(SELECT num FROM Messages WHERE server = {message.guild.id} ORDER BY date DESC LIMIT {d_ago} ) as mes_num"""
)[0][0]
await message.channel.send(
f"A média de mensagens dos últimos {d_ago} dias é de {avg}")
def GetBackup(table: str):
"""Returns a CSV file with the backup of the given table """
tables = db.custom_retrieve(
"SELECT table_name FROM information_schema.tables WHERE table_schema='public'"
)
valid_table = False
for t in tables:
print(t[0])
print(table)
if (table == t[0]):
valid_table = True
continue
if (not valid_table):
return None
o_path = path.join(getcwd(), f"{table}.csv")
db.copy_to(o_path, table)
return o_path
async def getRankByWeek(message, client):
args = comms.ParseArguments(message.content)
d_ago = 0
points = "messages"
if args != None:
if ("c" in args.keys()):
points = "chars"
if ("b" in args.keys()):
d_ago = int(args["b"])
if (d_ago) < 0:
await message.channel.send("Não há registros do futuro")
return
date = datetime.now().date()
date -= timedelta(days=date.weekday() + (d_ago * 7))
data = db.custom_retrieve(f""" SELECT user_id,{points}
FROM Rank WHERE date = '{date}' AND server_id = {message.guild.id}
ORDER BY {points} DESC LIMIT 20""")
if len(data) == 0:
await message.channel.send(
f"Não há registro do ranking de {d_ago} semanas atrás")
return
if (points == "messages"):
points = "mensagens"
elif (points == "chars"):
points = "caracteres"
table = GenTableRank(data, message.guild, client)
embed = utils.genEmbed(
"Ranking",
table,
descp=f"Ranking de membros por {points} \nenviadas na semana")
await message.channel.send(embed=embed, content=None)