async def send_message_security(message: Message, state = FSMContext):
text = message.text
cur.execute("select middle_name, first_name, last_name from tabEmployee where "
"telegramid=%d" % message.from_user.id)
mas = cur.fetchall()
cur.execute("select telegramid from tabEmployee where employment_type='Охрана'")
data = cur.fetchall()
for i in data:
await bot.send_message(i[0], "<b>" + text + "</b>" + "\nот " + mas[0][0] + " " + mas[0][1][0]
+ "." + mas[0][2][0] + ".")
await message.answer(text="Сообщение отправленно", reply_markup=menu_concierge )
await employer.work.set()
async def on_startup_notify_users(dp: Dispatcher):
text = "Пожалуйста войдите в аккаунт, нажав соответствующую кнопку"
conn.commit()
cur.execute("select name from tabTelegramUsers")
data = cur.fetchall()
cur.execute("select telegramid from tabEmployee")
data += cur.fetchall()
for i in data:
try:
await dp.bot.send_message(i[0], text, reply_markup=menu)
except:
print("WARRING: user without telegramid")
async def call_security(message:Message, sate=FSMContext):
await send_message.security.set()
conn.commit()
cur.execute("select telegramid from tabEmployee where employment_type='Охрана'")
data = cur.fetchall()
cur.execute("select middle_name, first_name, last_name, employment_type, location from tabEmployee where "
"telegramid=%d" % message.from_user.id)
mas = cur.fetchall()
from_employee = "Сообщение от: " + mas[0][0] + " " + mas[0][1] + " " + mas[0][2] + "\nДолжность: " + mas[0][3] + \
"\nМестоположение: " + mas[0][4]
if data != [(None,)]:
for i in data:
await bot.send_message(i[0], from_employee)
await message.answer("Введите сообщение для охраны", reply_markup=back_menu)
else:
await message.answer("Нет охранников", reply_markup=menu_concierge)
await employer.work.set()
async def send_feedback():
while(1):
conn.commit()
cur.execute("select name, answer, modified_by from tabTelegramUsers where answer!=' ' and enable=0")
data = cur.fetchall()
if(data):
for throw in data:
await bot.send_message(int(throw[0]), "Сообщение от: " + throw[2] + "\n" + throw[1])
teleid = int(throw[0])
cur.execute("delete from tabTelegramUsers where name=%d" %teleid)
conn.commit()
cur.execute("select name, answer, modified_by from tabTelegramUsers where "
"answer!=' ' and enable=1")
data = cur.fetchall()
if (data):
for throw in data:
await bot.send_message(int(throw[0]), "Сообщение от: " + throw[2] + "\n" + throw[1])
teleid = int(throw[0])
cur.execute("update tabTelegramUsers set answer=' ' where name=%d" %teleid)
conn.commit()
async def show_guests(message: Message, state=FSMContext):
await message.answer("Таблица гостей: \n")
params = [datetime.now() - timedelta(hours=24), datetime.now()]
conn.commit()
sql = "select subject from tabTask where project='Добавить гостя' and creation between ? and ?"
cur.execute(sql, params)
str = cur.fetchall()
a = []
for i in range(len(str)):
a.append(str[i][0])
str = "\n".join(a)
await message.answer(str)
async def login(message: Message, state=FSMContext):
conn.commit()
r = message.text
pattern = r'(\+7|8|7).*?(\d{3}).*?(\d{3}).*?(\d{2}).*?(\d{2})'
mes = message.from_user.id
if re.match(pattern, r) and (len(r) == 11 and r[0] == '8'
or len(r) == 12 and r[0] == '+'):
phone = format_phone(r)
if len(phone) == 12:
phone = phone.replace("+7", "8")
mas = []
mas.append(mes)
mas.append(phone)
sql_employeer = "SELECT name FROM tabEmployee WHERE name = %s" % phone
sql = "SELECT phone FROM tabTelegramUsers WHERE enable=1 and phone =%s" % phone
cur.execute(sql)
if (cur.fetchall()):
await message.answer("Вы вошли в личный кабинет",
reply_markup=menu_customer)
await user_status.logined.set()
else:
cur.execute(sql_employeer)
if (cur.fetchall()):
await message.answer(
text="Вы вошли в личный кабинет сотрудника",
reply_markup=menu_concierge)
cur.execute("update tabEmployee set telegramid=? where name=?",
mas)
conn.commit()
await employer.work.set()
else:
await message.answer(text="Ошибка аутентификации",
reply_markup=menu)
await state.finish()
else:
await message.answer(
text="Неверный формат телефона, пожалуйста повторите попытку")
await log_in.auth.set()
async def get_phone(message: Message, state: FSMContext):
r = message.text
pattern = r"\b\+?[7,8](\s*\d{3}\s*\d{3}\s*\d{2}\s*\d{2})\b"
mas = []
if re.match(pattern, r) and ((len(r) == 11 and (r[0] == '8') or
(len(r) == 12 and r[0] == '+'))):
cur.execute("select full_name from tabTelegramUsers where name = %s" %
message.from_user.id)
customer = cur.fetchall()[0][0]
phone = r
phone = format_phone(r)
if len(phone) == 12:
for i in range(1, 11):
phone = phone.replace("+7", "8")
await state.update_data(phone=phone)
data = await state.get_data()
sql = "select count(name)+1 from tabTask "
cur.execute(sql)
answerDb = cur.fetchall()
sql = "INSERT INTO tabTask (name, subject, project, creation, owner, status) VALUES (%s, %s, %s, %s, %s, 'Open')"
count_task = "TASK-2021-" + str(answerDb[0][0])
mas.append(count_task)
mas.append(data.get("guest_name") + " " + data.get("phone"))
mas.append("Добавить гостя")
mas.append(str(datetime.datetime.now()))
mas.append(customer)
cur.execute(sql, mas)
conn.commit()
await message.answer("Гость успешно добавлен",
reply_markup=menu_customer)
await user_status.logined.set()
else:
await message.answer(
"Неверный формат номера телефона. Пожалуйста, введите номер правильно."
)
await add_guest.get_phone.set()
async def find_guest(message: Message, state=employer.find):
params = "%" + message.text + "%"
cur.execute("select subject from tabTask where project='Добавить гостя' and subject like '%s'" % params)
print(params)
str = cur.fetchall()
print(str)
a = []
if str != []:
for i in range(len(str)):
a.append(str[i][0])
str = "\n".join(a)
await message.answer(str)
await employer.work.set()
else:
await message.answer("Гость не найден")
await employer.work.set()
async def enter_reg(message: Message):
conn.commit()
mes = message.from_user.id
cur.execute("select name from tabTelegramUsers where name=%d" % mes)
if (cur.fetchall()):
await message.answer(
"Вы уже зарегистрированны, либо ваш аккаунт еще не подтвердили.",
reply_markup=menu)
else:
await message.answer(f"Вы выбрали {message.text}",
reply_markup=ReplyKeyboardRemove())
await message.answer(
"При желании вы всегда можете выйти в главное меню, нажав кнопку отмена",
reply_markup=cancel)
await message.answer("Введите ФИО")
await reg.fio.set()
#sheet = wb.active
#sheet['A1'] = 'Гость'
#sheet['B1'] = 'Заказчик'
#sheet['C1'] = 'Дата'
#sheet['D1'] = 'Проект'
#sheet['E1'] = 'Сервис'
#wb.save('Archive.xlsx')
book = openpyxl.load_workbook('Archive.xlsx')
sheet = book.active
k = sheet.max_row + 1
while(1):
conn.commit()
params = [datetime.now() - timedelta(days=150), datetime.now() - timedelta(hours=24)]
sql = "select name, subject, owner, creation from tabTask where project='Добавить гостя' and creation between ? and ?"
cur.execute(sql, params)
data = cur.fetchall()
sql = "delete from tabTask where creation between ? and ?"
cur.execute(sql, params)
if(data):
for i in data:
params=[]
for j in i:
params.append(j)
params.append("Добавить гостя")
params.append("Консьерж")
sheet[k][0].value = params[1]
sheet[k][1].value = params[2]
sheet[k][2].value = params[3]
sheet[k][3].value = params[4]
sheet[k][4].value = params[5]
book.save("Archive.xlsx")