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)