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)