def get_active_notifications(self): while True: Dbconnetor.execute_insert_query("SET TIME ZONE 'Europe/Moscow';") notifications = Dbconnetor.execute_select_many_query( """SELECT request_id, sender_user_id, message_text FROM toparents_bot.user_requests WHERE request_status = 'NEW'""") for notification in notifications: admin_list = Botuser.get_admins() request_text = notification[2] sender_id = notification[1] request_id = notification[0] sender_username = Botuser.get_username(sender_id) send_text = ('#request\nОТ: {}\n\n{}'.format( sender_username, request_text)) for admin in admin_list: user = Botuser(uid=admin, bot=self.bot) keyboard = KeyboardHelper.reply_to_user_request_keyboard( user=user, sender_id=sender_id, request_id=request_id) sent_message = user.send_message(chat_id=user.uid, text=send_text, keyboard=keyboard) user.save_request_message( request_id=request_id, admin_id=user.uid, message_id=sent_message.message_id) time.sleep(1) Dbconnetor.execute_insert_query( "UPDATE toparents_bot.user_requests SET request_status = 'SENT' WHERE request_id = '{}' " .format(request_id)) time.sleep(5)
def send_main_test_results(self): dbconnector = Dbconnetor() summ = 0 result = self.dbconnector.execute_select_many_query( "SELECT answer from test_bot.user_answers WHERE user_id = {} AND test_type = 'MAIN_TEST' AND status = 'ACTIVE'" .format(self.uid)) for row in result: summ += int(row[0]) if summ <= 2: message_index = 'RESULT_MESSAGE_1' elif summ <= 4: message_index = 'RESULT_MESSAGE_2' elif summ <= 7: message_index = 'RESULT_MESSAGE_3' else: message_index = 'RESULT_MESSAGE_4' result_template = self.select_message(message_index=message_index) positive_answer = self.select_positive_answer() all_questions = dbconnector.count_questions() percentage = int(round((positive_answer / all_questions) * 100, 0)) send_text = ('=============================\n\n') send_text += self.select_message( message_index='SEND_PERCENTAGE').format(percentage) send_text += ('\n-----------------------------\n') send_text += result_template send_text += ('\n=============================') self.bot.send_message(chat_id=self.uid, text=send_text)
def save_request(self, message_text): request_id = uuid.uuid4() Dbconnetor.execute_insert_query(""" INSERT INTO toparents_bot.user_requests ( request_id, sender_user_id, "message_text", request_status ) VALUES ( '{}', {}, '{}', 'NEW' ); """.format(request_id, self.uid, message_text))
def send_post_to_users(self, post_index): dbconnector = Dbconnetor() users = dbconnector.execute_select_many_query( "SELECT user_id from core.users WHERE aggregator_bot_join_date IS NOT NULL AND aggregator_bot_block_date IS NULL") post = self.select_message(post_index) for user in users: self.send_message(chat_id=user[0], text=post) time.sleep(1)
def join_aggrbot(self, last_name, first_name, username, ref_key='Notset', lang='rus'): Dbconnetor.execute_insert_query(""" INSERT INTO core.users ( ref_id, lang, interface, user_id, last_name, first_name, username, aggregator_bot_join_date) VALUES ( '{0}', '{1}', 'TG', {2}, '{3}', '{4}', '{5}', current_timestamp ) ON CONFLICT ON CONSTRAINT idx_users DO UPDATE SET aggregator_bot_join_date = current_timestamp;""".format(ref_key, lang, self.uid, last_name, first_name, username))
def change_user_state(self, user_state): query = """ INSERT INTO toparents_bot.user_state_toparents_bot ( user_id, user_state ) VALUES ( '{}', '{}' ) ON CONFLICT ON CONSTRAINT pk_user_state_user_id DO UPDATE SET user_state = '{}';""".format(self.uid, user_state, user_state) Dbconnetor.execute_insert_query(query)
def send_message(self, chat_id=None, message_index=None, text=None, keyboard=None): if not text: text = self.select_message(message_index=message_index) if not chat_id: chat_id = self.uid try: if keyboard: return self.bot.send_message(chat_id=chat_id, text=text, reply_markup=keyboard, parse_mode='Markdown') else: return self.bot.send_message(chat_id=chat_id, text=text, parse_mode='Markdown') except telebot.apihelper.ApiException: Dbconnetor.execute_insert_query("""UPDATE core.users SET aggregator_bot_block_date = '{}' WHERE user_id = '{}' """.format(datetime.datetime.now(), chat_id))
def reply_to_request_handler(call, user, bot): sender_user = call.data.split('_')[1] request_id = call.data.split('_')[2] user.change_user_state('RESPONSE_{}_{}'.format(sender_user, request_id)) bot.edit_message_text(chat_id=call.message.chat.id, message_id=call.message.message_id, text=call.message.text) send_text = ('{}\n\nВведите ответ:'.format(call.message.text)) try: bot.send_message(chat_id=call.message.chat.id, text=send_text) except telebot.apihelper.ApiException: Dbconnetor.execute_insert_query( """UPDATE core.users SET aggregator_bot_block_date = '{}' WHERE user_id = '{}' """.format(datetime.datetime.now(), call.message.chat.id))
def send_custom_post(self, post_index): users = Dbconnetor.execute_select_many_query( "SELECT user_id from core.users WHERE aggregator_bot_join_date IS NOT NULL") keyboard = KeyboardHelper.yes_no_answers(user=self, post_index=post_index) send_text = self.select_message(post_index) for user in users: self.send_message(chat_id=user[0], text=send_text, keyboard=keyboard)
def get_admins(): result = Dbconnetor.execute_select_many_query("""SELECT user_id FROM toparents_bot.bot_admin WHERE status = 'ENABLE'""") admins_list = [] if result: for row in result: admins_list.append(row[0]) return admins_list
def get_message_ids(self, request_id): result = Dbconnetor.execute_select_many_query("""SELECT message_id, user_id FROM toparents_bot.request_messages WHERE request_id = '{}'""".format(request_id)) message_list = [] if result: for row in result: message_list.append(row) return message_list
def get_username(uid): result = Dbconnetor.execute_select_query( "SELECT username, first_name, last_name from core.users WHERE user_id = {}".format( uid)) if result[1] != 'None': return (result[1] + ' ' + result[2]) else: return result[0]
def select_message(self, message_index): lang = self.get_user_lang() if not lang: lang = 'rus' result = Dbconnetor.execute_select_query("""SELECT text FROM toparents_bot.messages WHERE lang = '{0}' AND message_index = '{1}'""".format(lang, message_index)) if result: return str(result[0])
def check_status(user, ref_key, last_name, first_name, username): dbconnector = Dbconnetor() if not user.isauth(): lang = user.check_status_new_user(ref_key) if lang: """ Добавление пользователя в базу с полученным языком """ """ Отправка первого вопроса """ user.join_to_bot_users(lang=lang, ref_key=ref_key, last_name=last_name, first_name=first_name, username=username) user.send_message(message_index='JOIN_MESSAGE') max_count_questions = dbconnector.count_questions() max_count_additional_questions = dbconnector.count_additional_questions( ) question_to_send = user.select_question_number_to_send() additional_question_to_send = user.select_addtional_question_number_to_send( ) if question_to_send <= max_count_questions: user.send_question(question_num=question_to_send) elif additional_question_to_send <= max_count_additional_questions: user.send_additional_question( question_num=additional_question_to_send, test_type='ADD_TEST') else: user.send_message(message_index='GO_TO_AGGR') else: user.send_select_lang_message() else: max_count_questions = dbconnector.count_questions() question_to_send = user.select_question_number_to_send() if question_to_send <= max_count_questions: user.send_question(question_num=question_to_send) else: user.send_message(message_index='GO_TO_AGGR')
def text_message_handler(bot, user, input_value): user_state = user.getstate() if user_state: if user_state.split('_')[2].isdigit(): dbconnector = Dbconnetor() current_question = int(user_state.split('_')[2]) question_to_send = current_question + 1 user.save_answer(question_num=current_question, answer=input_value, test_type='ADD_TEST') max_count_additional_questions: int = dbconnector.count_additional_questions( ) if question_to_send <= max_count_additional_questions: user.send_additional_question(question_num=question_to_send, test_type='ADD_TEST') else: user.change_user_state('') user.send_message('GO_TO_AGGR') else: user.change_user_state('') user.send_message(message_index='HELLO_MESSAGE') else: user.send_message(message_index='HELLO_MESSAGE')
def getstate(self): status = Dbconnetor.execute_select_query( "SELECT user_state from toparents_bot.user_state_toparents_bot WHERE user_id = {}".format(self.uid)) if status: return status[0]
from dbconnector import Dbconnetor import starting_helper from notificator import Notificator from post_helper import enter_post_index, enter_custom_post_index logging.basicConfig( filename='errors.log', level=logging.INFO, format='%(asctime)s.%(msecs)03d %(levelname)s %(module)s - %(funcName)s: %(message)s', datefmt='%Y-%m-%d %H:%M:%S') logger = telebot.logger telebot.logger.setLevel(logging.INFO) use_proxy = Dbconnetor.get_config_parameter('proxy', 'global') if use_proxy: apihelper.proxy = {'https': 'https://{}'.format(use_proxy)} TOKEN = Dbconnetor.get_config_parameter('api_token', 'aggr_bot') bot = telebot.TeleBot(TOKEN, threaded=True) notificator = Notificator(bot=bot) @bot.message_handler(commands=['start']) def handlestart(m): user = Botuser(uid=m.chat.id, bot=bot) try: starting_helper.stating_handler(user=user, message=m) except: logging.exception(str(m)) logging.exception('Got exception on main handler')
def save_request_message(self, request_id, admin_id, message_id): Dbconnetor.execute_insert_query(""" INSERT INTO toparents_bot.request_messages (message_id, user_id, request_id, status) VALUES ('{}', '{}', '{}', 'SENT') """.format(message_id, admin_id, request_id))
import logging import telebot from telebot import apihelper from dbconnector import Dbconnetor from bot_user import Botuser logging.basicConfig( filename='errors_aggr.log', level=logging.INFO, format='%(asctime)s.%(msecs)03d %(levelname)s %(module)s - %(funcName)s: %(message)s', datefmt='%Y-%m-%d %H:%M:%S') logger = telebot.logger #apihelper.proxy = {'https': 'https://*****:*****@77.83.185.165:8000'} telebot.logger.setLevel(logging.INFO) dbconnector = Dbconnetor() TOKEN = dbconnector.get_config_parameter('api_token', 'aggr_bot') bot = telebot.TeleBot(TOKEN, threaded=True) @bot.message_handler(commands=['start']) def handlestart(m): try: user = Botuser(uid=m.chat.id, bot=bot) last_name = m.from_user.last_name first_name = m.from_user.first_name username = m.from_user.username user.join_aggrbot(last_name=last_name, first_name=first_name, username=username, ref_key='Notset', lang='rus' ) bot.send_message(chat_id=m.chat.id, text='Привет. Чат бот находится в разработке, скоро все будет готово') except: logging.exception(str(m))
def __init__(self, uid, bot): self.uid = uid self.bot = bot self.dbconnector = Dbconnetor()
def get_user_lang(self): lang = Dbconnetor.execute_select_query( "SELECT lang from core.users WHERE users.user_id = {}".format(self.uid)) if lang: return lang[0]
class Botuser(): def __init__(self, uid, bot): self.uid = uid self.bot = bot self.dbconnector = Dbconnetor() def isauth(self): lang = self.dbconnector.execute_select_query( "SELECT lang from core.users WHERE users.user_id = {}".format( self.uid)) if lang: return lang[0] def check_status_new_user(self, ref_key): result = self.dbconnector.execute_select_query( "SELECT lang FROM core.ref_keys WHERE ref_keys.ref_id = '{}' and ref_keys.interface = 'TG'" .format(ref_key)) if result: return result[0] def check_status_exist_user(self): pass def select_message(self, message_index): lang = self.isauth() if not lang: lang = 'rus' result = self.dbconnector.execute_select_query( """SELECT text FROM test_bot.messages WHERE lang = '{0}' AND message_index = '{1}'""" .format(lang, message_index)) if result: return result[0] def send_select_lang_message(self): text = self.select_message(message_index='START_MESSAGE') keyboard = select_language_keyboard() self.bot.send_message(chat_id=self.uid, text=text, reply_markup=keyboard) def send_message(self, message_index): text = self.select_message(message_index=message_index) self.bot.send_message(chat_id=self.uid, text=text) def select_question(self, question_num, test_type): lang = self.isauth() if not lang: lang = 'rus' result = self.dbconnector.execute_select_query( """SELECT text FROM test_bot.test_questions WHERE (lang = '{0}') AND (num = {1}) AND (test_type = '{2}')""" .format(lang, question_num, test_type)) if result: return result[0] def send_question(self, question_num, test_type='MAIN_TEST'): text = self.select_question(question_num=question_num, test_type=test_type) keyboard = question_answers(user=self, question_num=question_num) self.bot.send_message(chat_id=self.uid, text=text, reply_markup=keyboard) def send_additional_question(self, question_num, test_type='ADD_TEST'): text = self.select_question(question_num=question_num, test_type=test_type) if question_num == 2: keyboard = additional_question_gender_answers(user=self) else: keyboard = additional_question_remove_keyboard() self.change_user_state('{}_{}'.format(test_type, question_num)) self.bot.send_message(chat_id=self.uid, text=text, reply_markup=keyboard) def join_to_bot_users(self, lang, last_name, first_name, username, ref_key='Notset'): self.dbconnector.execute_insert_query(""" INSERT INTO core.users ( ref_id, lang, interface, user_id, last_name, first_name, username, test_bot_join_date) VALUES ( '{0}', '{1}', 'TG', {2}, '{3}', '{4}', '{5}', current_timestamp ) ON CONFLICT ON CONSTRAINT idx_users DO UPDATE SET lang = '{1}';""".format(ref_key, lang, self.uid, last_name, first_name, username)) self.dbconnector.execute_insert_query(""" INSERT INTO test_bot.users_state ( user_id ) VALUES ({0}) ON CONFLICT ON CONSTRAINT idx_users_state_user_id DO NOTHING;""".format(self.uid)) def join_aggrbot(self, last_name, first_name, username, ref_key='Notset', lang='rus'): self.dbconnector.execute_insert_query(""" INSERT INTO core.users ( ref_id, lang, interface, user_id, last_name, first_name, username, aggregator_bot_join_date) VALUES ( '{0}', '{1}', 'TG', {2}, '{3}', '{4}', '{5}', current_timestamp ) ON CONFLICT ON CONSTRAINT idx_users DO UPDATE SET aggregator_bot_join_date = current_timestamp;""".format( ref_key, lang, self.uid, last_name, first_name, username)) def save_answer(self, question_num, answer, test_type): self.dbconnector.execute_insert_query(""" INSERT INTO test_bot.user_answers (user_id, answer, status, question_num, test_type) VALUES ({}, '{}', 'ACTIVE', {} , '{}');""".format( self.uid, answer, question_num, test_type)) def reset_results(self): self.dbconnector.execute_insert_query(""" UPDATE test_bot.user_answers SET status = 'DELETED' WHERE user_id = {}; """.format(self.uid)) def select_question_number_to_send(self): max_count = self.dbconnector.execute_select_query( """SELECT question_num FROM test_bot.user_answers WHERE user_id = {} AND status = 'ACTIVE' AND test_type = 'MAIN_TEST' ORDER BY question_num DESC LIMIT 1""" .format(self.uid)) if max_count: return int(max_count[0]) + 1 else: return 1 def select_addtional_question_number_to_send(self): max_count = self.dbconnector.execute_select_query( """SELECT question_num FROM test_bot.user_answers WHERE user_id = {} AND status = 'ACTIVE' AND test_type = 'ADD_TEST' ORDER BY question_num DESC LIMIT 1""" .format(self.uid)) if max_count: return int(max_count[0]) + 1 else: return 1 def select_positive_answer(self): positive_answers = self.dbconnector.execute_select_query( """SELECT COUNT(answer) FROM test_bot.user_answers WHERE user_id = {} AND status = 'ACTIVE' AND test_type = 'MAIN_TEST' AND answer = '1'""" .format(self.uid)) if positive_answers: return int(positive_answers[0]) else: return 0 def getstate(self): status = self.dbconnector.execute_select_query( "SELECT user_state from test_bot.users_state WHERE user_id = {}". format(self.uid)) if status: return status[0] def change_user_state(self, user_state): self.dbconnector.execute_insert_query(""" UPDATE test_bot.users_state SET user_state = '{1}' WHERE user_id = {0}; """.format(self.uid, user_state)) def send_main_test_results(self): dbconnector = Dbconnetor() summ = 0 result = self.dbconnector.execute_select_many_query( "SELECT answer from test_bot.user_answers WHERE user_id = {} AND test_type = 'MAIN_TEST' AND status = 'ACTIVE'" .format(self.uid)) for row in result: summ += int(row[0]) if summ <= 2: message_index = 'RESULT_MESSAGE_1' elif summ <= 4: message_index = 'RESULT_MESSAGE_2' elif summ <= 7: message_index = 'RESULT_MESSAGE_3' else: message_index = 'RESULT_MESSAGE_4' result_template = self.select_message(message_index=message_index) positive_answer = self.select_positive_answer() all_questions = dbconnector.count_questions() percentage = int(round((positive_answer / all_questions) * 100, 0)) send_text = ('=============================\n\n') send_text += self.select_message( message_index='SEND_PERCENTAGE').format(percentage) send_text += ('\n-----------------------------\n') send_text += result_template send_text += ('\n=============================') self.bot.send_message(chat_id=self.uid, text=send_text)
def save_answer(self, answer, test_type): Dbconnetor.execute_insert_query(""" INSERT INTO toparents_bot.user_answers (user_id, answer, status, question_num, post_type) VALUES ({}, '{}', 'ACTIVE', 1 , '{}');""".format( self.uid, answer, test_type))
from bot_user import Botuser from dbconnector import Dbconnetor import starting_helper import time logging.basicConfig( filename='errors.log', level=logging.INFO, format= '%(asctime)s.%(msecs)03d %(levelname)s %(module)s - %(funcName)s: %(message)s', datefmt='%Y-%m-%d %H:%M:%S') logger = telebot.logger telebot.logger.setLevel(logging.INFO) dbconnector = Dbconnetor() use_proxy = dbconnector.get_config_parameter('proxy', 'global') if use_proxy: apihelper.proxy = {'https': 'https://{}'.format(use_proxy)} TOKEN = dbconnector.get_config_parameter('api_token', 'test_bot') bot = telebot.TeleBot(TOKEN, threaded=True) @bot.message_handler(commands=['start']) def handlestart(m): user = Botuser(uid=m.chat.id, bot=bot) try: if user.isauth(): max_count_questions = dbconnector.count_questions() max_count_additional_questions = dbconnector.count_additional_questions( )
def request_update_staus(self, request_id, new_staus, resposne_text): now = datetime.datetime.now() query = """ UPDATE toparents_bot.user_requests SET request_status = '{}', response_text = '{}', response_datetime = '{}' WHERE request_id = '{}' """.format(new_staus, resposne_text, now, request_id) Dbconnetor.execute_insert_query(query)