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):
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):
# 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 = """
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