def build_page(self, group_username, only_admins=True):
query = """
SELECT
m.user_id,
COUNT(m.msg_id) AS leaderboard,
u_ref.name,
u_ref.last_name,
u_ref.username,
RANK() OVER (ORDER BY COUNT(m.msg_id) DESC)
FROM messages AS m
LEFT OUTER JOIN users_ref AS u_ref
USING (user_id)
WHERE m.group_id = %s
AND m.message_date > date_trunc('week', now())
GROUP BY m.user_id, u_ref.name, u_ref.last_name, u_ref.username
"""
lst_and_time = self.get_list_from_cache()
if lst_and_time is None:
extract = database.query_r(query, self.group_id)
self.cache_the_list(extract)
cached_sec_ago = 1
else:
extract, cached_at = lst_and_time
cached_sec_ago = int(time.time() - cached_at)
updated_ago_string = utils.round_seconds(cached_sec_ago, self.lang, short=True)
pages = Pages(extract, self.page)
footer_buttons = keyboards.check_groupleaderboard_in_private_button(self.lang, self.group_id) if only_admins else None
reply_markup = pages.build_buttons(
base=self.buttons_callback_base(),
only_admins=only_admins,
footer_buttons=footer_buttons
)
text = get_lang.get_string(self.lang, "pre_groupleaderboard").format(escape_markdown(group_username))
text += "\n_{}: {}_".format(
utils.get_lang.get_string(self.lang, "latest_update"),
updated_ago_string
)
text += "\n\n"
first_number_of_page = pages.first_number_of_page()
offset = first_number_of_page - 1
for user in pages.chosen_page_items():
offset += 1 # for before IT numeration
if only_admins: # it means it's in a group
text += "{}) [{}](tg://user?id={}): {}\n".format(
user[5],
utils.replace_markdown_chars(utils.truncate(user[2], M_C_G)),
user[0],
utils.sep_l(user[1], self.lang)
)
else: # it's a private chat
text += "{}) {}: {}\n".format(
user[5],
escape_markdown("@"+str(user[4]) if user[4] is not None else user[2]),
utils.sep_l(user[1], self.lang)
)
return text, reply_markup
def build_page(self):
# Thank https://stackoverflow.com/a/46496407/8372336 to make clear this query
query = """
SELECT
members.*,
supergroups.lang,
supergroups_ref.title,
supergroups_ref.username,
extract(epoch from supergroups.joined_the_bot at time zone 'utc') AS dt,
supergroups.nsfw,
RANK() OVER(ORDER BY members.amount DESC),
supergroups.category
FROM
-- Window function to get only de last_date:
(SELECT last_members.group_id,last_members.amount
FROM
(SELECT *, ROW_NUMBER() OVER (PARTITION BY group_id
ORDER BY updated_date DESC) AS row FROM members) AS last_members
WHERE last_members.row=1) AS members
-- Joins with other tables
LEFT JOIN supergroups
ON members.group_id = supergroups.group_id
LEFT JOIN supergroups_ref
ON supergroups.group_id = supergroups_ref.group_id
WHERE (supergroups.banned_until IS NULL OR supergroups.banned_until < now())
AND lang = %s
AND supergroups.bot_inside IS TRUE
"""
lst_and_time = self.get_list_from_cache()
if lst_and_time is None:
extract = database.query_r(query, self.region)
self.cache_the_list(extract)
cached_sec_ago = 1
else:
extract, cached_at = lst_and_time
cached_sec_ago = int(time.time() - cached_at)
updated_ago_string = utils.round_seconds(cached_sec_ago, self.lang, short=True)
if self.category != "":
extract = [i for i in extract if i[self.INDEX_CATEGORY] == self.category]
pages = Pages(extract, self.page)
callback_base = self.buttons_callback_base()
reply_markup = pages.build_buttons(base=callback_base, footer_buttons=keyboards.filter_category_button(self.lang, callback_base, pages.chosen_page))
emoji_region = supported_langs.COUNTRY_FLAG[self.region]
text = get_lang.get_string(self.lang, "pre_leadermember").format(emoji_region)
if self.category != "":
text += "\n{}: {}".format(get_lang.get_string(self.lang, "category"), get_lang.get_string(self.lang, "categories")[categories.CODES[self.category]])
text += "\n_{}: {}_".format(
utils.get_lang.get_string(self.lang, "latest_update"),
updated_ago_string
)
text += "\n\n"
for group in pages.chosen_page_items():
nsfw = emojis.NSFW if group[6] is True else ""
new = emojis.NEW if (group[5]+self.NEW_INTERVAL) > time.time() else ""
text += "{}) {}[{}](t.me/{}): {}{}\n".format(
group[7],
nsfw,
utils.replace_markdown_chars(utils.truncate(group[3], M_C_P)),
group[4],
utils.sep_l(group[1], self.lang),
new)
return text, reply_markup
def build_page(self):
query = """
SELECT
m.group_id,
COUNT (m.group_id) AS leaderboard,
s_ref.title,
s_ref.username,
s.nsfw,
extract(epoch from s.joined_the_bot at time zone 'utc') AS dt,
RANK() OVER (ORDER BY COUNT(m.group_id) DESC),
s.lang,
s.category
FROM messages AS m
LEFT OUTER JOIN supergroups_ref AS s_ref
ON s_ref.group_id = m.group_id
LEFT OUTER JOIN supergroups AS s
ON s.group_id = m.group_id
WHERE m.message_date > date_trunc('week', now())
AND (s.banned_until IS NULL OR s.banned_until < now())
AND s.lang = %s
AND s.bot_inside IS TRUE
GROUP BY m.group_id, s_ref.title, s_ref.username, s.nsfw, dt, s.banned_until, s.lang, s.category, s.bot_inside
"""
lst_and_time = self.get_list_from_cache()
if lst_and_time is None:
extract = database.query_r(query, self.region)
self.cache_the_list(extract)
cached_sec_ago = 1
else:
extract, cached_at = lst_and_time
cached_sec_ago = int(time.time() - cached_at)
updated_ago_string = utils.round_seconds(cached_sec_ago, self.lang, short=True)
if self.category != "":
extract = [i for i in extract if i[self.INDEX_CATEGORY] == self.category]
pages = Pages(extract, self.page)
callback_base = self.buttons_callback_base()
reply_markup = pages.build_buttons(base=callback_base, footer_buttons=keyboards.filter_category_button(self.lang, callback_base, pages.chosen_page))
emoji_region = supported_langs.COUNTRY_FLAG[self.region]
text = get_lang.get_string(self.lang, "pre_leadermessage").format(emoji_region)
if self.category != "":
text += "\n{}: {}".format(get_lang.get_string(self.lang, "category"), get_lang.get_string(self.lang, "categories")[categories.CODES[self.category]])
text += "\n_{}: {}_".format(
utils.get_lang.get_string(self.lang, "latest_update"),
updated_ago_string
)
text += "\n\n"
for group in pages.chosen_page_items():
nsfw = emojis.NSFW if group[4] is True else ""
new = emojis.NEW if (group[5]+self.NEW_INTERVAL) > time.time() else ""
text += "{}) {}[{}](t.me/{}): {}{}\n".format(
group[6],
nsfw,
utils.replace_markdown_chars(utils.truncate(group[2], M_C_P)),
group[3],
utils.sep_l(group[1], self.lang),
new
)
return text, reply_markup
def build_page(self):
query = """
WITH myconst AS
(SELECT
s.lang,
AVG(vote)::float AS overall_avg
FROM votes AS v
LEFT OUTER JOIN supergroups_ref AS s_ref
ON s_ref.group_id = v.group_id
LEFT OUTER JOIN supergroups AS s
ON s.group_id = v.group_id
GROUP BY s.banned_until, s.bot_inside, s.lang
HAVING
(s.banned_until IS NULL OR s.banned_until < now())
AND COUNT(vote) >= %s
AND s.bot_inside IS TRUE)
SELECT
*,
RANK() OVER (PARTITION BY sub.lang ORDER BY bayesan DESC)
FROM (
SELECT
v.group_id,
s_ref.title,
s_ref.username,
COUNT(vote) AS amount,
ROUND(AVG(vote), 1)::float AS average,
s.nsfw,
extract(epoch from s.joined_the_bot at time zone 'utc') AS dt,
s.lang,
s.category,
-- (WR) = (v ÷ (v+m)) × R + (m ÷ (v+m)) × C
-- * R = average for the movie (mean) = (Rating)
-- * v = number of votes for the movie = (votes)
-- * m = minimum votes required to be listed in the Top 250 (currently 1300)
-- * C = the mean vote across the whole report (currently 6.8)
( (COUNT(vote)::float / (COUNT(vote)+%s)) * AVG(vote)::float + (%s::float / (COUNT(vote)+%s)) * (m.overall_avg) ) AS bayesan
FROM votes AS v
LEFT OUTER JOIN supergroups_ref AS s_ref
ON s_ref.group_id = v.group_id
LEFT OUTER JOIN supergroups AS s
ON s.group_id = v.group_id
LEFT OUTER JOIN myconst AS m
ON (s.lang = m.lang)
GROUP BY v.group_id, s_ref.title, s_ref.username, s.nsfw, s.banned_until, s.lang, s.category, s.bot_inside, s.joined_the_bot, m.overall_avg
HAVING
(s.banned_until IS NULL OR s.banned_until < now())
AND COUNT(vote) >= %s
AND s.lang = %s
AND s.bot_inside IS TRUE
) AS sub;
"""
lst_and_time = self.get_list_from_cache()
if lst_and_time is None:
extract = database.query_r(
query,
self.MIN_REVIEWS,
self.MIN_REVIEWS,
self.MIN_REVIEWS,
self.MIN_REVIEWS,
self.MIN_REVIEWS,
self.region
)
self.cache_the_list(extract)
cached_sec_ago = 1
else:
extract, cached_at = lst_and_time
cached_sec_ago = int(time.time() - cached_at)
updated_ago_string = utils.round_seconds(cached_sec_ago, self.lang, short=True)
if self.category != "":
extract = [i for i in extract if i[self.INDEX_CATEGORY] == self.category]
pages = Pages(extract, self.page)
callback_base = self.buttons_callback_base()
reply_markup = pages.build_buttons(base=callback_base, footer_buttons=keyboards.filter_category_button(self.lang, callback_base, pages.chosen_page))
emoji_region = supported_langs.COUNTRY_FLAG[self.region]
text = get_lang.get_string(self.lang, "pre_leadervote").format(self.MIN_REVIEWS, emoji_region)
if self.category != "":
text += "\n{}: {}".format(get_lang.get_string(self.lang, "category"), get_lang.get_string(self.lang, "categories")[categories.CODES[self.category]])
text += "\n_{}: {}_".format(
utils.get_lang.get_string(self.lang, "latest_update"),
updated_ago_string
)
text += "\n\n"
for group in pages.chosen_page_items():
nsfw = emojis.NSFW if group[5] is True else ""
new = emojis.NEW if (group[6]+self.NEW_INTERVAL > time.time()) else ""
text += "{}) {}[{}](t.me/{}): {}{}|{}{}\n".format(
group[10],
nsfw,
utils.replace_markdown_chars(utils.truncate(group[1], M_C_P)),
group[2],
group[4],
emojis.STAR,
utils.sep_l(group[3], self.lang),
new
)
return text, reply_markup
def group_rank_text(group_id, lang):
strings = get_lang.get_string(lang, "group_rank")
rank = cache_groups_rank.get_group_cached_rank(group_id)
if rank is None:
return strings['None']
text = strings['title']
# by messages
try:
text += "\n\n"
text += strings['by_messages'].format(
rank[cache_groups_rank.BY_MESSAGES][cache_groups_rank.REGION])
text += "\n"
text += strings['position'].format(
utils.sep_l(
rank[cache_groups_rank.BY_MESSAGES][cache_groups_rank.RANK],
lang))
text += "\n"
text += strings['messages'].format(
utils.sep_l(
rank[cache_groups_rank.BY_MESSAGES][cache_groups_rank.VALUE],
lang))
text += "\n"
text += strings['updated'].format(
utils.get_lang.get_string(lang, "latest_update"),
utils.round_seconds(
int(time.time() - rank[cache_groups_rank.BY_MESSAGES][
cache_groups_rank.CACHED_AT]), lang))
except KeyError:
pass
# by members
try:
text += "\n\n"
text += strings['by_members'].format(
rank[cache_groups_rank.BY_MEMBERS][cache_groups_rank.REGION])
text += "\n"
text += strings['position'].format(
utils.sep_l(
rank[cache_groups_rank.BY_MEMBERS][cache_groups_rank.RANK],
lang))
text += "\n"
text += strings['members'].format(
utils.sep_l(
rank[cache_groups_rank.BY_MEMBERS][cache_groups_rank.VALUE],
lang))
text += "\n"
text += strings['updated'].format(
utils.get_lang.get_string(lang, "latest_update"),
utils.round_seconds(
int(time.time() - rank[cache_groups_rank.BY_MEMBERS][
cache_groups_rank.CACHED_AT]), lang))
except KeyError:
pass
# by votes average
try:
text += "\n\n"
text += strings['by_votes'].format(
rank[cache_groups_rank.BY_VOTES][cache_groups_rank.REGION])
text += "\n"
text += strings['position'].format(
utils.sep_l(
rank[cache_groups_rank.BY_VOTES][cache_groups_rank.RANK],
lang))
text += "\n"
text += strings['votes'].format(
rank[cache_groups_rank.BY_VOTES][cache_groups_rank.VALUE][0],
utils.sep_l(
rank[cache_groups_rank.BY_VOTES][cache_groups_rank.VALUE][1],
lang))
text += "\n"
text += strings['updated'].format(
utils.get_lang.get_string(lang, "latest_update"),
utils.round_seconds(
int(time.time() - rank[cache_groups_rank.BY_VOTES][
cache_groups_rank.CACHED_AT]), lang))
except KeyError:
pass
return text
def aboutyou(bot, update):
user_id = update.message.from_user.id
# query = """
# WITH tleft AS (
# SELECT main.user_id, u.lang, main.num_msgs, main.num_grps, main.rnk
# FROM (
# SELECT
# user_id,
# num_grps,
# num_msgs,
# DENSE_RANK() OVER(ORDER BY num_msgs DESC, num_grps DESC, user_id DESC) rnk
# FROM (
# SELECT
# user_id,
# COUNT(distinct group_id) AS num_grps,
# COUNT(*) AS num_msgs
# FROM messages
# WHERE message_date > date_trunc('week', now())
# GROUP BY user_id
# ) AS sub
# ) AS main
# LEFT OUTER JOIN users AS u
# USING (user_id)
# WHERE u.weekly_own_digest = TRUE AND user_id = %s
# AND bot_blocked = FALSE
# )
# , tright AS (
# SELECT main.user_id, main.group_id, s_ref.title, s_ref.username, main.m_per_group, main.pos
# FROM (
# SELECT user_id, group_id, COUNT(user_id) AS m_per_group,
# ROW_NUMBER() OVER (
# PARTITION BY group_id
# ORDER BY COUNT(group_id) DESC
# ) AS pos
# FROM messages
# WHERE message_date > date_trunc('week', now()) AND user_id = %s
# GROUP BY group_id, user_id
# ) AS main
# LEFT OUTER JOIN supergroups_ref AS s_ref
# USING (group_id)
# ORDER BY m_per_group DESC
# )
# SELECT l.user_id, l.lang, l.num_msgs, l.num_grps, l.rnk, r.title, r.username, r.m_per_group, r.pos
# FROM tleft AS l
# INNER JOIN tright AS r
# USING (user_id)
# """
#######################
# WARNING!!!! #
#######################
# No more using the query to the db, but using the scheduled cache.
# by the way the result is returned in the very same form
# so it can be changed anytime
# FOLLOWING LINES ARE COMMENTED TO NOT EXECUTE THE QUERY
#extract = database.query_r(query, user_id, user_id)
#extract = cache_users_stats.group_extract(extract)[0]
user_cache, latest_update = cache_users_stats.get_cached_user(user_id)
lang = utils.get_db_lang(user_id)
if user_cache is None:
text = get_lang.get_string(lang, "you_inactive_this_week")
else:
text = get_lang.get_string(lang, "this_week_you_sent_this") + "\n\n"
groups = user_cache[1]
for group in groups:
title = group[0]
username = group[1]
m_per_group = group[2]
pos_per_group = group[3]
text += get_lang.get_string(lang,
"messages_in_groups_position").format(
utils.sep_l(m_per_group, lang),
username,
utils.sep_l(pos_per_group, lang))
# global stats
text += "\n" + get_lang.get_string(
lang, "you_globally_this_week").format(
utils.sep_l(user_cache[0][2], lang),
utils.sep_l(user_cache[0][3], lang),
utils.sep_l(user_cache[0][4], lang))
text += "\n\n{}: {}.".format(
utils.get_lang.get_string(lang, "latest_update"),
utils.round_seconds(int(time.time() - latest_update), lang))
utils.send_message_long(bot, chat_id=user_id, text=text)