def send_notifications(self):
if self.started:
for table in self.tables:
for sheet in table.sheets:
amount_re = re.compile(r'Надо(\n|\s|\t)+отправить.*')
#print(sheet.wks.get_all_records())
list_of_cells = sheet.wks.findall(amount_re)
for cell in list_of_cells:
row = str(cell.row)
number = sheet.wks.acell(sheet.number + row).value.replace('-', '').replace('(', '').replace(')', '')
if (number[0]=="7"):
number="+" + number
elif (number[0]==8):
number = number.replace("8","+7",1)
db = DbHelper()
chat_id = db.execute_select("SELECT chat_id FROM {}.{} where phone_number='{}'"
.format(
self.config.get("postgresql","schema"),
self.config.get("postgresql","users_table"),
number))[0][0]
if ("sms" in str(cell.value)):
Twilio.send_sms(sheet.wks.acell(sheet.text + row).value,number)
sheet.wks.update_acell(sheet.sended+row,"Да")
LogDBHandler.log(Actions.finish_sending,"sms",number)
else:
if self.send_message(sheet.wks.acell(sheet.text + row).value,chat_id):
sheet.wks.update_acell(sheet.sended+row,"Да")
sheet.wks.update_acell(sheet.status+row,"отправлено")
time.sleep(float(self.config.get("botsettings","sending_interval")))
def __init__(self, table_id):
self.table_id = table_id
db = DbHelper()
sheets_db = db.execute_select("SELECT * FROM notificator.google_sheets WHERE table_id='{}'".format(table_id))
self.sheets = []
for sheet in sheets_db:
self.sheets.append(NotificationSheet(sheet[1],sheet[2],sheet[3],sheet[4],sheet[5],sheet[6],sheet[7]))
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, self.config.get("botsettings","messenger_name"), number, "", "success", "user", chat_id)
Twilio.send_sms(twillio_message, number)
LogDBHandler.log(Actions.code_sended,self.config.get("botsettings","messenger_name"),number,"","success","bot",chat_id)
self.send_message(self.config.get("messages","confirmation_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 unsubscribe(self, first_name, chat_id):
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,
chat_id))
LogDBHandler.log(Actions.unsubscribed, self.config.get("botsettings","messenger_name"), self.get_phone_number(chat_id), "", "success", "user", chat_id)
self.send_message(self.config.get("messages","unsubscribed_message"), chat_id)
def get_phone_number(self, chat_id):
db = DbHelper()
rows = db.execute_select("SELECT phone_number FROM {}.{} WHERE chat_id='{}'"
.format(
self.config.get("postgresql","schema"),
self.config.get("postgresql","users_table"),
chat_id))
if len(rows)!=0:
return str(rows[0][0])
else:
return ""
def list_users(self, first_name, chat_id):
result = ""
db=DbHelper()
rows = db.execute_select("select user_name, phone_number, chat_id, (select caption from {0}.{1} where id=status), role from {0}.{2}"
.format(
self.config.get("postgresql","schema"),
self.config.get("postgresql","statuse_table"),
self.config.get("postgresql","users_table")))
for row in rows:
result+="Имя: {}, Номер: {}, Статус: {}, Роль: {}\n".format(row[0],row[1],row[3],row[4])
self.send_message(result,chat_id)
def get_user_role(self, first_name, chat_id):
db = DbHelper()
rows = db.execute_select("SELECT role 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:
return str(rows[0][0])
else:
return None
def __init__(self, token):
self.config=ConfigParser()
self.config.read(CONFIG_FILE_NAME,"utf_8_sig")
self.started = True;
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:
self.tables.append(NotificationTable(table[1]))
self.TOKEN = token
self.URL = "https://api.telegram.org/bot{}/".format(token)
LogDBHandler.log(Actions.start, self.config.get("botsettings","messenger_name"),"", "", "success", "")
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 log(action,
messenger,
phone_number,
message,
result,
direction,
user_id=0,
additional_info='',
sended_json="{}",
recieved_json="{}"):
db = DbHelper()
sql = "INSERT INTO notificator.logs (action_, messenger, phone_number, message, date_time, sended_json, recieved_json, result, direction, user_id, additional_info) values ('{}','{}','{}','{}', to_timestamp('{}', 'YY/mm/DD HH24:MI:SS'), '{}', '{}', '{}', '{}', {}, '{}')".format(
action, messenger, phone_number, message,
datetime.now().strftime("%Y/%m/%d %H:%M:%S"), sended_json,
recieved_json, result, direction, user_id, additional_info)
db.execute_sql(sql)
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))
LogDBHandler.log(Actions.registred, self.config.get("botsettings","messenger_name"), self.get_phone_number(chat_id), "", "success", "user", chat_id)
self.send_message(self.config.get("messages","share_number_message"), chat_id, self.build_keyboard(self.config.get("messages","button_caption")))
else:
self.send_message(self.config.get("messages","already_subscribed_message"), chat_id)
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:
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(self.config.get("messages","subscribed_message"), chat_id)
LogDBHandler.log(Actions.registred,self.config.get("botsettings","messenger_name"),number,"","success","bot",chat_id)
else:
LogDBHandler.log(Actions.registred,self.config.get("botsettings","messenger_name"),number,"","failed","bot",chat_id,"wrong confirmation code")
self.send_message(self.config.get("messages","wrong_code_message"), chat_id)
def get_database_param(param_name):
db = DbHelper()
rows = db.execute_select(
"SELECT param_value FROM {}.{} WHERE param_name = '{}'".format(
"notificator", "params", param_name))
return rows[0][0]
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, "")