def try_process_number(self, first_name, chat_id, number):
db = DbHelper()
rows = db.execute_select("SELECT * FROM {}.{} WHERE user_name = '{}' AND chat_id='{}'"
.format(
self.config.get("postgresql","schema"),
self.config.get("postgresql","users_table"),
first_name,
chat_id))
if len(rows)!=0 and rows[0][4]==1:
twillio_message = str(random.randint(0,9)) + str(random.randint(0,9)) + str(random.randint(0,9)) + str(random.randint(0,9))
if (number[0]=="7"):
number="+" + number
db.execute_sql("UPDATE {}.{} SET phone_number='{}', sms_code='{}', status={} WHERE user_name='{}' and chat_id='{}'"
.format(
self.config.get("postgresql","schema"),
self.config.get("postgresql","users_table"),
number,
twillio_message,
2,
first_name,
chat_id))
LogDBHandler.log(Actions.phone_number_recieved, configuration.get_database_param("messanger_name"), number, "", "success", "user", chat_id)
Twilio.send_sms(twillio_message, number)
LogDBHandler.log(Actions.code_sended,configuration.get_database_param("messanger_name"),number,"","success","bot",chat_id)
self.send_message(configuration.get_database_param("confirmation_message"), chat_id)
def unsubscribe(self, first_name, chat_id):
phone_number = self.get_phone_number(chat_id)
try:
db = DbHelper()
db.execute_sql("DELETE FROM {}.{} WHERE user_name = '{}' AND phone_number = '{}'"
.format(
self.config.get("postgresql","schema"),
self.config.get("postgresql","users_table"),
first_name,
phone_number))
LogDBHandler.log(Actions.unsubscribed, configuration.get_database_param("messanger_name"), phone_number, "", "success", "bot", chat_id)
self.send_message(configuration.get_database_param("unsubscribed_message"), chat_id)
except Exception as e:
LogDBHandler.log(Actions.unsubscribed, configuration.get_database_param("messanger_name"), phone_number, str(e).replace("/","//").replace("'","\""), "failed", "bot", chat_id)
def __init__(self, token):
self.config=ConfigParser()
self.config.read(CONFIG_FILE_NAME,"utf_8_sig")
self.started = True;
self.TOKEN = token
self.URL = "https://api.telegram.org/bot{}/".format(token)
LogDBHandler.log(Actions.start, configuration.get_database_param("messanger_name"),"", "", "success", "")
def send_message(self, text, chat_id, reply_markup=None):
response = '{}'
try:
unparsedtext = text
print(text)
text = urllib.parse.quote_plus(text)
url = self.URL + "sendMessage?text={}&chat_id={}&parse_mode=HTML".format(text, chat_id)
#role = self.get_user_role(chat_id)
#if role=="ADMIN":
# reply_markup = self.build_admin_keyboard()
if reply_markup:
url += "&reply_markup={}".format(reply_markup)
response = self.get_url(url)
print(url)
LogDBHandler.log(Actions.message, configuration.get_database_param("messanger_name"), self.get_phone_number(chat_id), unparsedtext[0:100], "success", "bot", chat_id, "",json.dumps(url) ,json.dumps(response.replace("'", '\\"')))
return True
except Exception as e:
LogDBHandler.log(Actions.message, configuration.get_database_param("messanger_name"), self.get_phone_number(chat_id), unparsedtext[0:100], "failed", "bot", chat_id, str(e).replace("/","//").replace("'","\""), json.dumps(url), json.dumps(response.replace("'", '\\"')))
return False
def subscribe(self, first_name, chat_id):
db = DbHelper()
rows = db.execute_select("SELECT * FROM {}.{} WHERE user_name = '{}' AND chat_id = '{}'"
.format(
self.config.get("postgresql","schema"),
self.config.get("postgresql","users_table"),
first_name,
chat_id))
if len(rows)==0:
db.execute_sql("INSERT INTO {}.{} (user_name, chat_id, status) VALUES ('{}', '{}', {})"
.format(
self.config.get("postgresql","schema"),
self.config.get("postgresql","users_table"),
first_name,
chat_id,
1))
#keyboard = self.build_keyboard(telegram.KeyboardButton(text="Поделиться номером телефона", request_contact=True))
self.send_message(configuration.get_database_param("share_number_message"), chat_id, self.build_keyboard(configuration.get_database_param("button_caption")))
else:
self.send_message(configuration.get_database_param("already_subscribed_message"), chat_id)
def try_process_code(self, first_name, chat_id, number):
db = DbHelper()
rows = db.execute_select("SELECT * FROM {}.{} WHERE user_name = '{}' AND chat_id='{}'"
.format(
self.config.get("postgresql","schema"),
self.config.get("postgresql","users_table"),
first_name,
chat_id))
if len(rows)!=0 and rows[0][4]==2:
try:
if int(number)==rows[0][5]:
db.execute_sql("UPDATE {}.{} SET status={} WHERE user_name='{}' and chat_id='{}'"
.format(
self.config.get("postgresql","schema"),
self.config.get("postgresql","users_table"),
3,
first_name,
chat_id))
self.send_message(configuration.get_database_param("subscribed_message"), chat_id)
LogDBHandler.log(Actions.registred,configuration.get_database_param("messanger_name"),self.get_phone_number(chat_id),str(number),"success","bot",chat_id)
return True
else:
self.send_message(configuration.get_database_param("wrong_code_message"), chat_id)
LogDBHandler.log(Actions.registred,configuration.get_database_param("messanger_name"),self.get_phone_number(chat_id),str(number),"failed","bot",chat_id,"wrong confirmation code")
return True
except Exception as e:
self.send_message(configuration.get_database_param("wrong_code_message"), chat_id)
LogDBHandler.log(Actions.registred,configuration.get_database_param("messanger_name"),self.get_phone_number(chat_id),str(number),"failed","bot",chat_id,"wrong confirmation code")
return True
else:
return False
def echo_all(self, updates):
for update in updates["result"]:
try:
text = update["message"]["text"]
chat = update["message"]["chat"]["id"]
LogDBHandler.log(Actions.message, configuration.get_database_param("messanger_name"), self.get_phone_number(chat), text, "success", "user", chat,"","{}", json.dumps(update).replace("'","\""))
first_name = update["message"]["from"]["first_name"]
role = self.get_user_role(chat)
if text=="/start" and self.started:
self.subscribe(first_name,chat)
elif text==configuration.get_database_param("unsubscribe") and self.started:
self.unsubscribe(first_name,chat)
elif text==configuration.get_database_param("disengage") and role=="ADMIN" and self.started==True:
self.started=False
self.send_message("Работа приостановлена.", chat)
LogDBHandler.log(Actions.stop, configuration.get_database_param("messanger_name"), self.get_phone_number(chat), text, "success", "user", chat,"","{}", json.dumps(update).replace("'","\""))
elif text==configuration.get_database_param("engage") and role=="ADMIN":
if self.started==False:
self.started=True
self.send_message("Бот запущен.", chat)
LogDBHandler.log(Actions.start, configuration.get_database_param("messanger_name"), self.get_phone_number(chat), text, "success", "user", chat,"","{}", json.dumps(update).replace("'","\""))
else:
self.send_message("Бот уже работает!", chat)
elif text==configuration.get_database_param("list_subscribers") and role=="ADMIN":
print(text)
self.list_users(first_name,chat)
elif text==configuration.get_database_param("help"):
self.list_help(first_name,chat)
elif text==configuration.get_database_param("bot_status"):
if (self.started==True):
self.send_message("Запущен", chat)
else:
self.send_message("Остановлен", chat)
elif self.try_process_code(first_name, chat, text)==False:
self.send_message(configuration.get_database_param("unknown_comand"),chat)
except KeyError:
try:
chat = update["message"]["chat"]["id"]
first_name = update["message"]["from"]["first_name"]
number = update["message"]["contact"]["phone_number"]
self.try_process_number(first_name, chat, number)
except KeyError:
chat = update["message"]["chat"]["id"]
self.send_message(configuration.get_database_param("unsupported"),chat)
except Exception as e:
LogDBHandler.log(Actions.error, configuration.get_database_param("messanger_name"), "", str(e).replace("/","//").replace("'","\""), "fail", "bot", 0,"","{}", json.dumps(update).replace("'","\""))
print(e)
def send_notifications(self):
if self.started:
LogDBHandler.log(Actions.queue, configuration.get_database_param("messanger_name"), "", "Начал цикл", "success", "bot", 0, "")
db = DbHelper()
tables_db = db.execute_select("SELECT * FROM {}.{}".format(self.config.get("postgresql","schema"), self.config.get("postgresql","tables_dict")))
self.tables = []
for table in tables_db:
k = 0
while k < 3:
try:
self.tables.append(NotificationTable(table[1]))
except gspread.exceptions.APIError as e:
continue
break
for table in self.tables:
numbers=[]
LogDBHandler.log(Actions.queue, configuration.get_database_param("messanger_name"), "", "Начал обработку таблицы. ({})".format(table.table_id), "success", "bot", 0, "")
for sheet in table.sheets:
k = 0
while k < 3:
k += 1
try:
need_send_re = re.compile(r'(' + re.escape(configuration.get_database_param("need_send").strip()) + r')|(' + re.escape(configuration.get_database_param("need_send_sms").strip()) + r')', re.IGNORECASE)
#print(sheet.wks.get_all_records())
list_of_cells = sheet.wks.findall(need_send_re)
for cell in list_of_cells:
time.sleep(float(configuration.get_database_param("sending_interval"))/2)
print(cell.row)
row = str(cell.row)
sended = sheet.wks.acell(sheet.sended + row).value
if sended=="Да":
continue
print("needsend")
number = sheet.wks.acell(sheet.number + row).value.replace('-', '').replace('(', '').replace(')', '').replace(' ','')
text = sheet.wks.acell(sheet.text + row).value
date = sheet.wks.acell(sheet.date + row).value
print(date)
try:
datetime.datetime.strptime(date, '%d/%m/%Y %H:%M:%S')
except ValueError:
LogDBHandler.log(Actions.error, configuration.get_database_param("messanger_name"), "", text, "failed", "bot", 0, "Ошибка в таблице: неверный формат даты. Таблица: {0} Лист: {1} Строка: {2} ".format(str(table.table_id), str(sheet.sheet_id), str(row)))
continue
print("needsend")
status = sheet.wks.acell(sheet.status + row).value
if len(number)>0:
if (number[0]=="7"):
number="+" + number
elif (number[0]=="8"):
number = number.replace("8","+7",1)
else:
LogDBHandler.log(Actions.error, configuration.get_database_param("messanger_name"), "", text, "failed", "bot", 0, "Ошибка в таблице: не заполнен номер телефона в таблице. Таблица: {0} Лист: {1} Строка: {2} ".format(str(table.table_id), str(sheet.sheet_id), str(row)))
continue
if any(x.phone_number == number for x in numbers)==False and sended != "Да":
notification_number = NotificationNumber(number,sheet)
notification_number.messages.append(NotificationMessage(date,status,sended,text,row))
numbers.append(notification_number)
else:
for x in numbers:
if x.phone_number==number:
notification_number = x
notification_number.messages.append(NotificationMessage(date,status,sended,text,row))
except gspread.exceptions.APIError as e:
sheet.refresh()
print(sheet.sheet_id + ";" + sheet.table_id)
print(e)
continue
break
for number in numbers:
number.sort_by_date()
print(number.phone_number)
for message in number.messages:
print(message.date)
LogDBHandler.log(Actions.queue, configuration.get_database_param("messanger_name"), "", "Таблицу обработал ({}). Начинаю отправку сообщений.".format(str(table.table_id)), "success", "bot", 0, "")
for number in numbers:
number.sort_by_date()
for message in number.messages:
print(number.phone_number + ";" + message.date + ";" + message.text + ";" + message.status + ";" + message.sended + ";" + message.row)
i=0
while i < 3:
i += 1
try:
db = DbHelper()
dbrows = db.execute_select("SELECT chat_id FROM {}.{} where phone_number='{}' and status=3"
.format(
self.config.get("postgresql","schema"),
self.config.get("postgresql","users_table"),
number.phone_number))
if len(dbrows) != 0:
chat_id = dbrows[0][0]
if message.sended != "Да":
print("Sending...")
if (configuration.get_database_param("need_send_sms").strip().lower()==str(message.status).lower()) and Twilio.send_sms(message.text,number.phone_number):
number.sheet.wks.update_acell(number.sheet.sended+message.row,"Да")
print("Sended sms")
elif (configuration.get_database_param("need_send").strip().lower()==str(message.status).lower()) and self.send_message(message.text,chat_id):
print("Sended telegram")
number.sheet.wks.update_acell(number.sheet.sended+message.row,"Да")
else:
LogDBHandler.log(Actions.error, configuration.get_database_param("messanger_name"), number.phone_number, message.text, "failed", "bot", 0, "Ошибка в таблице: пользователь не зарегистрирован. Таблица: {0} Лист: {1} Строка: {2} ".format(str(table.table_id), str(number.sheet.sheet_id), str(message.row)))
time.sleep(float(configuration.get_database_param("sending_interval"))/2)
except gspread.exceptions.APIError as e:
number.sheet.refresh()
continue
break
LogDBHandler.log(Actions.queue, configuration.get_database_param("messanger_name"), "", "Отправку сообщений по таблице закончил ({})".format(table.table_id), "success", "bot", 0, "")
LogDBHandler.log(Actions.queue, configuration.get_database_param("messanger_name"), "", "Цикл отправки завершил.", "success", "bot", 0, "")
def list_help(self, first_name, chat_id):
msg = "Начать работу с ботом: {0}\nОтписаться: {1}\nПомощь: {2}".format("/start", configuration.get_database_param("unsubscribe"), configuration.get_database_param("help"))
msgadmin = "Запустить бота: {0}\nОстановить бота: {1}\nСтатус бота: {2}\nПолучить список пользователей: {3}".format(configuration.get_database_param("engage"), configuration.get_database_param("disengage"), configuration.get_database_param("bot_status"),configuration.get_database_param("list_subscribers"))
if self.get_user_role(chat_id)=="ADMIN":
msg = msg + "\n" + msgadmin
self.send_message(msg, chat_id)