def schedule_own_private_digest(bot, job, data):
start_in = 0
for i in data:
start_in += 0.1
user = i[0]
groups = i[1]
user_id = user[0]
lang = user[1]
tot_msg = user[2]
tot_grps = user[3]
tot_pos = user[4]
first_name = user[5]
text = get_lang.get_string(
lang, "hello_name").format(name=first_name) + "!\n"
text += get_lang.get_string(lang, "digest_of_the_week_global").format(
utils.sep_l(tot_msg, lang), utils.sep_l(tot_grps, lang),
utils.sep_l(tot_pos, lang))
reply_markup = keyboards.disable_private_own_weekly_digest_kb(lang)
# append the text of any group for the same user
for group in groups:
title = group[0]
username = group[1]
msg_g = group[2]
pos_g = group[3]
text += get_lang.get_string(lang,
"digest_of_the_week_detail").format(
utils.sep_l(msg_g, lang), username,
utils.sep_l(pos_g, lang))
text += "\n#weekly_private_digest"
# text completed can be scheduled
job.job_queue.run_once(send_one_by_one,
start_in,
context=[user_id, text, reply_markup])
def diff_percent(new, old, lang):
diff = new - old
diff_s = utils.sep_l(diff, lang) if diff < 0 else "+"+utils.sep_l(diff, lang)
try:
percent = round(diff*100/old, 2)
percent_s = (utils.sep_l(percent, lang) if percent < 0 else "+"+utils.sep_l(percent, lang))+"%"
except ZeroDivisionError:
percent_s = "—"
return diff_s, percent_s
def weekly_groups_digest(bot, job):
near_interval = '7 days'
far_interval = '14 days'
query = """
SELECT
group_id,
lang,
nsfw,
joined_the_bot
FROM supergroups
WHERE weekly_digest = TRUE AND bot_inside = TRUE
ORDER BY last_date DESC
"""
lst = database.query_r(query)
#############
# MESSAGES
############
query = """
SELECT
group_id,
COUNT(msg_id) AS msgs,
RANK() OVER(PARTITION BY s.lang ORDER BY COUNT(msg_id) DESC)
FROM messages
LEFT OUTER JOIN supergroups as s
USING (group_id)
WHERE
message_date > now() - interval %s
AND (s.banned_until IS NULL OR s.banned_until < now())
AND s.bot_inside IS TRUE
GROUP BY s.lang, group_id
"""
msgs_this_week = database.query_r(query, near_interval)
query = """
SELECT
group_id,
COUNT(msg_id) AS msgs,
RANK() OVER(PARTITION BY s.lang ORDER BY COUNT(msg_id) DESC)
FROM messages
LEFT OUTER JOIN supergroups as s
USING (group_id)
WHERE
message_date BETWEEN now() - interval %s AND now() - interval %s
AND (s.banned_until IS NULL OR s.banned_until < now())
AND s.bot_inside IS TRUE
GROUP BY s.lang, group_id
"""
msgs_last_week = database.query_r(query, far_interval, near_interval)
#############
# MEMBERS
############
query = """
SELECT
last_members.group_id,
last_members.amount,
RANK() OVER(PARTITION BY s.lang ORDER BY last_members.amount DESC)
FROM
(
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY group_id
ORDER BY updated_date DESC
) AS row
FROM members
) AS last_members
LEFT OUTER JOIN supergroups AS s
USING (group_id)
WHERE
last_members.row=1
AND (s.banned_until IS NULL OR s.banned_until < now())
AND s.bot_inside IS TRUE
"""
members_this_week = database.query_r(query)
query = """
SELECT
last_members.group_id,
last_members.amount,
RANK() OVER(PARTITION BY s.lang ORDER BY last_members.amount DESC)
FROM
(
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY group_id
ORDER BY updated_date DESC
) AS row
FROM members
WHERE updated_date <= now() - interval %s
) AS last_members
LEFT OUTER JOIN supergroups AS s
USING (group_id)
WHERE
last_members.row=1
AND (s.banned_until IS NULL OR s.banned_until < now())
AND s.bot_inside IS TRUE
"""
members_last_week = database.query_r(query, near_interval)
####################
# SUM AND AVG VOTES
####################
query = """
WITH myconst AS
(SELECT
s.lang,
AVG(vote)::float AS overall_avg
FROM votes AS v
LEFT OUTER JOIN supergroups AS s
ON s.group_id = v.group_id
WHERE (s.banned_until IS NULL OR s.banned_until < now() )
AND s.bot_inside IS TRUE
GROUP BY s.lang
HAVING COUNT(vote) >= %s)
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.bot_inside IS TRUE
) AS sub;
"""
this_week_votes_avg = database.query_r(
query,
leaderboards.VotesLeaderboard.MIN_REVIEWS,
leaderboards.VotesLeaderboard.MIN_REVIEWS,
leaderboards.VotesLeaderboard.MIN_REVIEWS,
leaderboards.VotesLeaderboard.MIN_REVIEWS,
leaderboards.VotesLeaderboard.MIN_REVIEWS
)
query = """
SELECT
group_id,
COUNT(vote) AS amount,
ROUND(AVG(vote), 1) AS average,
RANK() OVER(PARTITION BY s.lang ORDER BY ROUND(AVG(VOTE), 1)DESC, COUNT(VOTE)DESC)
FROM votes
LEFT OUTER JOIN supergroups AS s
USING (group_id)
WHERE vote_date <= now() - interval %s
GROUP BY group_id, s.lang, s.banned_until, s.bot_inside
HAVING
(s.banned_until IS NULL OR s.banned_until < now())
AND COUNT(vote) >= %s
AND s.bot_inside IS TRUE
"""
query = """
WITH myconst AS
(SELECT
s.lang,
AVG(vote)::float AS overall_avg
FROM votes AS v
LEFT OUTER JOIN supergroups AS s
ON s.group_id = v.group_id
WHERE (s.banned_until IS NULL OR s.banned_until < now() ) AND vote_date <= now() - interval %s
AND s.bot_inside IS TRUE
GROUP BY s.lang
HAVING COUNT(vote) >= %s)
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)
WHERE vote_date <= now() - interval %s
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.bot_inside IS TRUE
) AS sub;
"""
last_week_votes_avg = database.query_r(
query,
near_interval,
leaderboards.VotesLeaderboard.MIN_REVIEWS,
leaderboards.VotesLeaderboard.MIN_REVIEWS,
leaderboards.VotesLeaderboard.MIN_REVIEWS,
leaderboards.VotesLeaderboard.MIN_REVIEWS,
near_interval,
leaderboards.VotesLeaderboard.MIN_REVIEWS
)
##################
# ACTIVE USERS
##################
query = """
SELECT
group_id,
COUNT(DISTINCT user_id),
RANK() OVER(PARTITION BY s.lang ORDER BY COUNT(DISTINCT user_id) DESC)
FROM messages
LEFT OUTER JOIN supergroups AS s
USING (group_id)
WHERE
message_date > (now() - interval %s)
AND (s.banned_until IS NULL OR s.banned_until < now())
AND s.bot_inside IS TRUE
GROUP BY group_id, s.lang
"""
this_week_active_users = database.query_r(query, near_interval)
query = """
SELECT
group_id,
COUNT(DISTINCT user_id),
RANK() OVER(PARTITION BY s.lang ORDER BY COUNT(DISTINCT user_id) DESC)
FROM messages
LEFT OUTER JOIN supergroups AS s
USING (group_id)
WHERE
message_date BETWEEN (now() - interval %s) AND (now() - interval %s)
AND (s.banned_until IS NULL OR s.banned_until < now())
AND s.bot_inside IS TRUE
GROUP BY group_id, s.lang
"""
last_week_active_users = database.query_r(query, far_interval, near_interval)
start_in = 0
for group in lst:
start_in += 0.1
group_id = group[0]
lang = group[1]
msgs_new = 0
msgs_old = 0
msgs_pos_old = 0
msgs_pos_new = 0
members_new = 0
members_old = 0
members_pos_old = 0
members_pos_new = 0
sum_v_new = 0
avg_v_new = 0
sum_v_old = 0
avg_v_old = 0
avg_pos_old = 0
avg_pos_new = 0
act_users_new = 0
act_users_old = 0
act_users_pos_old = 0
act_users_pos_new = 0
for i in msgs_this_week:
if i[0] == group_id:
msgs_new = i[1]
msgs_pos_new = i[2]
break
for i in msgs_last_week:
if i[0] == group_id:
msgs_old = i[1]
msgs_pos_old = i[2]
break
for i in members_this_week:
if i[0] == group_id:
members_new = i[1]
members_pos_new = i[2]
break
for i in members_last_week:
if i[0] == group_id:
members_old = i[1]
members_pos_old = i[2]
break
for i in this_week_votes_avg:
if i[0] == group_id:
sum_v_new = i[3]
avg_v_new = i[4]
avg_pos_new = i[10]
break
for i in last_week_votes_avg:
if i[0] == group_id:
sum_v_old = i[3]
avg_v_old = i[4]
avg_pos_old = i[10]
break
for i in this_week_active_users:
if i[0] == group_id:
act_users_new = i[1]
act_users_pos_new = i[2]
break
for i in last_week_active_users:
if i[0] == group_id:
act_users_old = i[1]
act_users_pos_old = i[2]
break
diff_msg, percent_msg = diff_percent(msgs_new, msgs_old, lang)
diff_members, percent_members = diff_percent(members_new, members_old, lang)
diff_act, percent_act = diff_percent(act_users_new, act_users_old, lang)
text = get_lang.get_string(lang, "weekly_groups_digest").format(
# by messages
utils.sep_l(msgs_old, lang),
utils.sep_l(msgs_new, lang),
diff_msg, percent_msg,
utils.sep_l(msgs_pos_old, lang),
utils.sep_l(msgs_pos_new, lang),
# by members
utils.sep_l(members_old, lang),
utils.sep_l(members_new, lang),
diff_members, percent_members,
utils.sep_l(members_pos_old, lang),
utils.sep_l(members_pos_new, lang),
# by votes average
utils.sep_l(avg_v_old, lang), emojis.STAR, utils.sep_l(sum_v_old, lang),
utils.sep_l(avg_v_new, lang), emojis.STAR, utils.sep_l(sum_v_new, lang),
utils.sep_l(avg_pos_old, lang),
utils.sep_l(avg_pos_new, lang),
# by active users
utils.sep_l(act_users_old, lang),
utils.sep_l(act_users_new, lang),
diff_act, percent_act,
utils.sep_l(act_users_pos_old, lang),
utils.sep_l(act_users_pos_new, lang)
)
##############
# TOP n USERS
##############
query_top_users = """
SELECT
user_id,
COUNT(msg_id) AS num_msgs,
name,
RANK() OVER (ORDER BY COUNT(msg_id) DESC)
FROM messages AS m
LEFT OUTER JOIN users_ref AS u_ref
USING (user_id)
WHERE group_id = %s AND m.message_date > (now() - interval %s)
GROUP BY user_id, name
LIMIT %s
"""
top_users_of_the_group = database.query_r(query_top_users, group_id, near_interval, 10)
for user in top_users_of_the_group:
text += "{}) <a href=\"tg://user?id={}\">{}</a>: {}\n".format(
user[3],
user[0],
html.escape(utils.truncate(user[2], c.MAX_CHARS_LEADERBOARD_PAGE_GROUP)),
utils.sep_l(user[1], lang)
)
text += "\n#weekly_group_digest"
reply_markup = keyboards.disable_group_weekly_digest_kb(lang)
# schedule send
job.job_queue.run_once(
send_one_by_one_weekly_group_digest,
start_in,
context=[group_id, text, reply_markup]
)
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)
