def infoid_from_db(tgid):
if tgid > 0:
query = """
SELECT
lang,
region,
tg_lang,
bot_blocked,
banned_on,
banned_until,
weekly_own_digest,
weekly_groups_digest,
registered_at::timestamp(0),
message_date
FROM users
WHERE user_id = %s"""
extract = database.query_r(query, tgid, one=True)
if extract is None:
return "Not in the db"
text = ""
text += "lang: {}\n".format(extract[0])
text += "region: {}\n".format(extract[1])
text += "tg_lang: {}\n".format(extract[2])
text += "bot_blocked: {}\n".format(extract[3])
text += "banned_on: {}\n".format(extract[4])
text += "banned_until: {}\n".format(extract[5])
text += "weekly_own_digest: {}\n".format(extract[6])
text += "weekly_groups_digest: {}\n".format(extract[7])
text += "registered_at: {}\n".format(extract[8])
text += "message_date: {}\n".format(extract[9])
else:
query = """
SELECT
lang,
nsfw,
joined_the_bot,
banned_on,
banned_until,
ban_reason,
bot_inside,
last_date,
category
FROM supergroups
WHERE group_id = %s
"""
extract = database.query_r(query, tgid, one=True)
if extract is None:
return "Not in the db"
text = ""
text += "lang: {}\n".format(extract[0])
text += "nsfw: {}\n".format(extract[1])
text += "joined_the_bot: {}\n".format(extract[2])
text += "banned_on: {}\n".format(extract[3])
text += "banned_until: {}\n".format(extract[4])
text += "ban_reason: {}\n".format(extract[5])
text += "bot_inside: {}\n".format(extract[6])
text += "last_date: {}\n".format(extract[7])
text += "category: {}\n".format(
categories.CODES[extract[8]] if extract[8] is not None else None)
return text
def current_page_admin(bot, query):
group_id = query.message.chat.id
query_db = "SELECT lang FROM supergroups WHERE group_id = %s"
extract = database.query_r(query_db, group_id, one=True)
lang = extract[0] if extract is not None else None
query.answer(get_lang.get_string(lang, "already_this_page"),
show_alert=True)
def group_rank(bot, update):
query = "SELECT lang FROM supergroups WHERE group_id = %s"
lang = database.query_r(query, update.message.chat.id, one=True)[0]
update.message.reply_text(text=group_rank_text(update.message.chat.id,
lang),
parse_mode='HTML',
quote=False)
def language_private(bot, update):
query = "SELECT lang FROM users WHERE user_id = %s"
extract = database.query_r(query, update.message.from_user.id, one=True)
lang = extract[0]
text = get_lang.get_string(lang, "choose_your_lang")
reply_markup = keyboards.private_language_kb(lang, back=False)
update.message.reply_text(text=text, reply_markup=reply_markup)
def group_rank_private(bot, update, args):
user_id = update.message.from_user.id
lang = utils.get_db_lang(user_id)
if len(args) != 1:
text = get_lang.get_string(lang, "error_param_group_rank_private")
update.message.reply_text(text, parse_mode="HTML")
return
username = args[0]
if username.startswith("@"):
username = username.replace("@", "")
query = "SELECT group_id FROM supergroups_ref WHERE LOWER(username) = LOWER(%s)"
extract = database.query_r(query, username)
if len(extract) > 1:
print("error too many")
return
if len(extract) == 0:
# the group does not exist otherwise anything is returned and if None is NULL
text = get_lang.get_string(lang, "cant_check_this").format(
html.escape(username))
update.message.reply_text(text=text)
return
group_id = extract[0][0]
update.message.reply_text(text=group_rank_text(group_id, lang),
parse_mode="HTML")
def all_results_no_filters(self):
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 (PARTITION BY supergroups.lang 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 supergroups.bot_inside IS TRUE
"""
return database.query_r(query)
def get_groups_to_log(bot, job):
query = """
WITH m_table AS (
SELECT
last_members.group_id,
last_members.updated_date
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
)
SELECT
s.group_id
FROM supergroups AS s
LEFT OUTER JOIN m_table AS m
USING (group_id)
WHERE
s.bot_inside = TRUE
AND (m.updated_date < (now() - interval %s) OR m.updated_date IS NULL)
ORDER BY m.updated_date ASC NULLS FIRST
"""
extract = database.query_r(query, DEADLINE)
return extract
def vote_intro(group_id, lang):
query = "SELECT username, title FROM supergroups_ref WHERE group_id = %s"
extract = database.query_r(query, group_id, one=True)
if extract is None:
return None
else:
return get_lang.get_string(lang, "vote_this_group").format(group_id, extract[0], extract[1])
def all_results_no_filters(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.bot_inside IS TRUE
) AS sub;
"""
return database.query_r(
query,
self.MIN_REVIEWS,
self.MIN_REVIEWS,
self.MIN_REVIEWS,
self.MIN_REVIEWS,
self.MIN_REVIEWS
)
def region(bot, update):
query = "SELECT lang, region FROM users WHERE user_id = %s"
extract = database.query_r(query, update.message.from_user.id, one=True)
lang = extract[0]
region = extract[1]
text = get_lang.get_string(lang, "choose_region")
reply_markup = keyboards.private_region_kb(lang, region)
update.message.reply_text(text=text, reply_markup=reply_markup)
def settings_group(bot, update):
query_db = "SELECT lang FROM supergroups WHERE group_id = %s"
lang = database.query_r(query_db, update.message.chat.id, one=True)[0]
text = get_lang.get_string(lang, "choose_group_lang")
reply_markup = keyboards.main_group_settings_kb(lang)
update.message.reply_text(text=text,
reply_markup=reply_markup,
quote=False)
def group_lang_button(bot, query):
query_db = "SELECT lang FROM supergroups WHERE group_id = %s"
lang = database.query_r(query_db, query.message.chat.id, one=True)[0]
text = get_lang.get_string(lang, "choose_group_lang")
reply_markup = keyboards.select_group_lang_kb(lang)
query.answer()
try:
query.edit_message_text(text=text, reply_markup=reply_markup)
except TelegramError as e:
if str(e) != "Message is not modified": print(e)
def leaderboard(bot, update):
query = "SELECT lang, region FROM users WHERE user_id = %s"
extract = database.query_r(query, update.message.from_user.id, one=True)
lang = extract[0]
region = extract[1]
text = get_lang.get_string(lang, "generic_leaderboard").format(
supported_langs.COUNTRY_FLAG[region])
reply_markup = keyboards.generic_leaderboard_kb(lang, region)
update.message.reply_text(text=text,
reply_markup=reply_markup,
parse_mode="HTML")
def private_lang(bot, query):
query.answer()
query_db = "SELECT lang FROM users WHERE user_id = %s"
extract = database.query_r(query_db, query.from_user.id, one=True)
lang = extract[0]
text = get_lang.get_string(lang, "choose_your_lang")
reply_markup = keyboards.private_language_kb(lang)
try:
query.edit_message_text(text=text, reply_markup=reply_markup)
except TelegramError as e:
if str(e) != "Message is not modified": print(e)
def is_banned(bot, update):
query = "SELECT banned_until FROM supergroups WHERE group_id = %s"
extract = database.query_r(query, update.message.chat.id, one=True)
if extract is None:
ban = None
else:
if extract[0] is None or extract[0] < datetime.datetime.now():
ban = None
else:
ban = extract[0]
return ban # this returns None if not banned else the expiring date
def change_group_category(bot, query):
query_db = "SELECT lang, category FROM supergroups WHERE group_id = %s"
lang = database.query_r(query_db, query.message.chat.id, one=True)[0]
category = query.data.split(":")[1]
query.answer()
reply_markup = keyboards.group_categories_kb(lang, category)
try:
query_db = "UPDATE supergroups SET category = %s WHERE group_id = %s"
database.query_w(query_db, category, query.message.chat.id)
query.message.edit_reply_markup(reply_markup=reply_markup)
except TelegramError as e:
if str(e) != "Message is not modified": print(e)
def adult_menu(bot, query):
query_db = "SELECT lang, nsfw FROM supergroups WHERE group_id = %s"
extraction = database.query_r(query_db, query.message.chat.id, one=True)
lang = extraction[0]
nsfw = extraction[1]
text = get_lang.get_string(lang, "have_adult")
reply_markup = keyboards.adult_content_kb(lang, nsfw)
query.answer()
try:
query.edit_message_text(text=text, reply_markup=reply_markup)
except TelegramError as e:
if str(e) != "Message is not modified": print(e)
def stats_users(bot, update):
# total users grouped by regions
query = """
SELECT region, COUNT(user_id) AS amount
FROM users
GROUP BY region
ORDER BY amount DESC
"""
text = "<b>Total users per region:\n</b>"
extract = database.query_r(query)
for i in extract:
text += "— <b>{}:</b> {}\n".format(i[0], i[1])
# users grouped by regions didn't block the bot
query = """
SELECT region, COUNT(user_id) AS amount
FROM users
WHERE bot_blocked = FALSE
GROUP BY region
ORDER BY amount DESC
"""
text += "\n<b>Users didn't block the bot:\n</b>"
extract = database.query_r(query)
for i in extract:
text += "— <b>{}:</b> {}\n".format(i[0], i[1])
# users grouped by regions didn't block the bot active 7 days
query = """
SELECT region, COUNT(user_id) AS amount
FROM users
WHERE bot_blocked = FALSE AND message_date > (now() - interval '7 days')
GROUP BY region
ORDER BY amount DESC
"""
text += "\n<b>Didn't block the bot and active in the last 7 days per region:</b>\n"
extract = database.query_r(query)
for i in extract:
text += "— <b>{}:</b> {}\n".format(i[0], i[1])
update.message.reply_text(text=text, parse_mode='HTML')
def stats_groups(bot, update):
# total groups
query = """
SELECT lang, COUNT(group_id) AS amount
FROM supergroups
GROUP BY lang
ORDER BY amount DESC
"""
text = "<b>Total groups per lang:</b>\n"
extract = database.query_r(query)
for i in extract:
text += "— <b>{}:</b> {}\n".format(i[0], i[1])
# total groups bot not removed
query = """
SELECT lang, COUNT(group_id) AS amount
FROM supergroups
WHERE bot_inside = TRUE
GROUP BY lang
ORDER BY amount DESC
"""
text += "<b>\nTotal groups per lang didn't remove the bot:</b>\n"
extract = database.query_r(query)
for i in extract:
text += "— <b>{}:</b> {}\n".format(i[0], i[1])
# total groups bot inside and joined last 7 days
query = """
SELECT lang, COUNT(group_id) AS amount
FROM supergroups
WHERE bot_inside = TRUE AND last_date > (now() - interval '7 days')
GROUP BY lang
ORDER BY amount DESC
"""
text += "<b>\nTotal groups per lang didn't remove the bot and joined in the past 7 days:</b>\n"
extract = database.query_r(query)
for i in extract:
text += "— <b>{}:</b> {}\n".format(i[0], i[1])
update.message.reply_text(text=text, parse_mode='HTML')
def vote_link(bot, query):
group_id = query.message.chat.id
query_db = "SELECT lang FROM supergroups WHERE group_id = %s"
extraction = database.query_r(query_db, group_id, one=True)
lang = extraction[0]
text = get_lang.get_string(lang, "here_group_vote_link")
text += "\n\n{}".format(votelink.create_vote_link(group_id))
reply_markup = keyboards.vote_link_kb(lang)
query.answer()
try:
query.edit_message_text(text=text, reply_markup=reply_markup)
except TelegramError as e:
if str(e) != "Message is not modified": print(e)
def vote(bot, update, args):
user_id = update.message.from_user.id
lang = utils.get_db_lang(user_id)
if len(args) != 1:
text = get_lang.get_string(lang, "insert_param_vote")
update.message.reply_text(text, parse_mode="HTML")
return
username = args[0]
if username.startswith("@"):
username = username.replace("@", "")
query = """
SELECT s.group_id, s_ref.username, s_ref.title, v.vote, v.vote_date
FROM supergroups_ref AS s_ref
RIGHT JOIN supergroups AS s
ON s_ref.group_id = s.group_id
LEFT OUTER JOIN votes AS v
ON v.group_id = s.group_id
AND v.user_id = %s
WHERE LOWER(s_ref.username) = LOWER(%s)
AND s.bot_inside = TRUE
"""
extract = database.query_r(query, user_id, username)
if len(extract) == 0:
# the group does not exist otherwise anything is returned and if None is NULL
text = get_lang.get_string(lang, "cant_vote_this")
update.message.reply_text(text=text)
return
if len(extract) > 1:
print("error too many")
return
extract = extract[0]
text = get_lang.get_string(lang, "vote_this_group").format(
extract[0], extract[1], extract[2])
if extract[3] and extract[4] is not None:
stars = emojis.STAR * extract[3]
date = utils.formatted_date_l(extract[4].date(), lang)
text += "\n\n" + get_lang.get_string(lang, "already_voted").format(
stars, date)
if extract[3] and extract[4] is not None:
reply_markup = keyboards.change_vote_kb(extract[0], lang)
else:
text += "\n\n"
text += get_lang.get_string(lang, "vote_from_one_to_five")
reply_markup = keyboards.vote_group_kb(extract[0], lang)
update.message.reply_text(text=text, reply_markup=reply_markup)
def first_start(bot, update):
user_id = update.message.from_user.id
query = """
SELECT 1
FROM USERS
WHERE user_id = %s
FETCH FIRST 1 ROW ONLY
"""
extract = database.query_r(query, user_id, one=True)
if extract is None: # this is the first time the user starts the bot
# send region choose
guessed_lang = utils.guessed_user_lang(bot, update)
text = get_lang.get_string(guessed_lang, "choose_region")
reply_markup = keyboards.private_region_kb(guessed_lang, guessed_lang)
update.message.reply_text(text=text, reply_markup=reply_markup)
def lbpage_igl_group(bot, query, page, group_id_buttons):
group_id = query.message.chat.id
query_db = "SELECT lang FROM supergroups WHERE group_id = %s"
extract = database.query_r(query_db, group_id, one=True)
lang = extract[0]
leaderboard = leaderboards.GroupLeaderboard(lang=lang,
page=int(page),
group_id=group_id_buttons)
result = leaderboard.build_page(group_username=query.message.chat.username)
try:
query.edit_message_text(text=result[0],
reply_markup=result[1],
parse_mode=ParseMode.MARKDOWN,
disable_notification=True)
except TelegramError as e:
if str(e) != "Message is not modified": print(e)
def lbpage_igl_private(bot, query, page, group_id_buttons):
lang = utils.get_db_lang(query.from_user.id)
leaderboard = leaderboards.GroupLeaderboard(lang=lang,
page=int(page),
group_id=group_id_buttons)
query_db = "SELECT username FROM supergroups_ref WHERE group_id = %s LIMIT 1"
extract = database.query_r(query_db, group_id_buttons, one=True)
result = leaderboard.build_page(group_username=extract[0],
only_admins=False)
try:
query.edit_message_text(text=result[0],
reply_markup=result[1],
parse_mode=ParseMode.MARKDOWN,
disable_notification=True)
except TelegramError as e:
if str(e) != "Message is not modified": print(e)
def get_all_users_stats():
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,
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 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,
RANK() OVER (
PARTITION BY group_id
ORDER BY COUNT(group_id) DESC
) AS pos
FROM messages
WHERE message_date > date_trunc('week', now())
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)
"""
return group_extract(db.query_r(query))
def groupleaderboard_private_direct_link(bot, update, group_id):
user_id = update.message.from_user.id
lang = utils.get_db_lang(user_id)
page = 1
update.effective_chat.send_action('typing')
query_db = "SELECT username FROM supergroups_ref WHERE group_id = %s LIMIT 1"
extract = database.query_r(query_db, group_id, one=True)
leaderboard = leaderboards.GroupLeaderboard(lang=lang,
page=page,
group_id=group_id)
result = leaderboard.build_page(group_username=extract[0],
only_admins=False)
update.message.reply_text(text=result[0],
reply_markup=result[1],
parse_mode='MARKDOWN',
disable_notification=True)
def leave_banned_group(bot, update):
query_db = "SELECT lang, banned_until, ban_reason FROM supergroups WHERE group_id = %s"
extract = database.query_r(query_db, update.message.chat.id, one=True)
lang = extract[0]
banned_until = extract[1]
reason = extract[2]
shown_reason = html.escape(
reason) if reason is not None else get_lang.get_string(
lang, "not_specified")
shown_reason = "<code>{}</code>".format(shown_reason)
text = get_lang.get_string(lang, "banned_until_leave").format(
utils.formatted_datetime_l(banned_until.replace(microsecond=0), lang),
shown_reason)
update.message.reply_text(text=text, quote=False, parse_mode='HTML')
bot.leaveChat(update.message.chat.id)
query = "UPDATE supergroups SET bot_inside = FALSE WHERE group_id = %s"
database.query_w(query, update.message.chat.id)
def set_group_category(bot, query):
query_db = "SELECT lang, category FROM supergroups WHERE group_id = %s"
extract = database.query_r(query_db, query.message.chat.id, one=True)
if extract is None:
lang = None
current_category = None
else:
lang, current_category = extract
text = get_lang.get_string(lang, "choose_group_category")
query.answer()
reply_markup = keyboards.group_categories_kb(lang, current_category)
try:
query.edit_message_text(text=text,
reply_markup=reply_markup,
parse_mode='HTML')
except TelegramError as e:
if str(e) != "Message is not modified": print(e)
def leave_unsupported_chat(bot, update):
if update.message.chat.type == "group" or (
update.message.chat.type == "supergroup"
and update.message.chat.username is None):
query = "SELECT lang FROM supergroups WHERE group_id = %s"
extract = database.query_r(query, update.message.chat.id, one=True)
if extract is None:
lang = 'en'
else:
lang = extract[0]
text = get_lang.get_string(lang, "unsupported_chat")
text += utils.text_mention_creator(bot, update.message.chat.id)
update.message.reply_text(text=text, quote=False, parse_mode='HTML')
bot.leaveChat(update.message.chat.id)
query = "UPDATE supergroups SET bot_inside = FALSE WHERE group_id = %s"
database.query_w(query, update.message.chat.id)
return True
def leadermember(bot, update, args):
query = "SELECT lang, region FROM users WHERE user_id = %s"
extract = database.query_r(query, update.message.from_user.id, one=True)
lang = extract[0]
region = extract[1]
result = filter_private_leaderboards_params(bot, update, args, lang)
if result is None:
return
else:
page, category = result
leaderboard = MembersLeaderboard(lang, region, page, category)
result = leaderboard.build_page()
update.message.reply_text(
text=result[0],
reply_markup=result[1],
parse_mode=ParseMode.MARKDOWN,
disable_web_page_preview=True)
