Для кого эта статья
Предназначено для тех, кто задумывается о цифровизации предприятий. Телеграм-бот - это отличный способ сделать интерфейс между базой данных и сотрудником, у которого нет в данный момент доступа в корпоративную сеть (к своему компьютеру). Безусловно, мобильное приложение решает этот вопрос лучше, но затраты на создание/поддержание мобильного приложения несопоставимы с затратами на простого ТГ-бота (или даже средней сложности), то же относится и ко времени разработки/внедрения. Надеюсь, данная статья будет полезной для энтузиастов широкого внедрения ТГ-ботов как технологии цифровизации промышленных (и не только) предприятий.
Легенда
Мы находимся внутри корпоративной сети предприятия, у нас есть выход в Интернет, но снаружи нет открытых интерфейсов. Есть учетная система предприятия на основе базы данных MS SQL, к ней можно подключиться произвольному клиенту и выполнять запросы в рамках прав, определенных для этого клиента. Наша задача - позволить рабочему сообщить о поломке станка через ТГ-бот (у рабочего нет логина в корпоративную сеть и подключения к учетной системе).
Технологии
Бот будем разрабатывать на python с помощью библиотеки aiogram3, как это описано в серии обучающих статей @yakvenalex, например, https://habr.com/ru/companies/amvera/articles/820527/
Если эта практика цифровизации приживется, ботов будет много; чтобы как-то управлять всем этим и ориентироваться в сервисах, сразу проектируем их в микросервисной архитектуре - упаковываем в docker container, и запускать будем именно конейтенер.
Для демонстрации работоспособности MS SQL Server также запустим в docker container, согласно https://hub.docker.com/r/microsoft/mssql-server. Это просто эмуляция существующей базы данных учетной системы, на этом подробно останавливаться не будем, но поговорим о правах доступа.
Схема работы
Два слова по поводу слоя хранимых процедур, посредством которых бот будет получать и записывать данные в таблицы. Он необходим: 1) как уровень абстракции (чтобы отвязаться от структуры таблиц и иметь возможность ее менять не меняя кода бота); 2) чтобы ограничить права пользователя, под которым бот будет авторизоваться в рабочей базе данных (таким образом, даже при ошибке программиста, бот не сможет испортить произвольные данные в базе)
Подготовка MS SQL - эмуляция рабочей базы данных
Запускаем контейнер с SQL Server
docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<password>' \
-p 1433:1433 \
--name mssql-server \
-v sqlvolume:/var/opt/mssql \
-d mcr.microsoft.com/mssql/server:2022-latestДалее, подключаемся SSMS (или любым другим инструментом) как пользователь sa и выполняем следующий скрипт
-- Стартовый скрипт
create database testdb; -- Тестовая база данных для демонстрации
go;
use testdb;
-- Создаем таблицы для работы бота, возможно, их придется создать
-- и в продуктивной базе
create table bot_user_type( -- типы пользователей
id int identity primary key, -- первичный ключ
user_type varchar(32), -- тип пользователя
);
insert into bot_user_type(user_type)
values (N'Рабочий'), (N'Механик'), (N'Электрик'), (N'Администратор');
create table bot_user( -- пользователи бота
id int identity primary key, -- первичный ключ
user_name varchar(128), -- имя пользователя в ТГ
tg_id bigint, -- ИД в телеграм
f_bot_user_type int foreign key references bot_user_type(id) --тип пользователя
);
insert into bot_user(user_name, f_bot_user_type) -- фейковые пользователи для демонстрации
values(N'Петр Иванов', 2), (N'Василий Кузецов', 3);
create table issue( -- Поломки
id int identity primary key, -- первичный ключ
f_bot_user int foreign key references bot_user(id), -- Подавший заявку
created smalldatetime, -- Дата и время подачи
f_bot_user_type int foreign key references bot_user_type(id), -- 1 - нужен механик, 2 - электрик
f_bot_user_resp int foreign key references bot_user(id), -- ответственный исполнитель
closed smalldatetime -- дата и время закрытия
);
go;
create schema bot; -- специальная схема, в которой будет находиться слой хранимых процедур
go;
create login tgbot with password = '<VeryStrongPassword>'; -- пользователь, под которым бот будет авторизоваться
create user tgbot for login tgbot;
go;
grant execute on schema ::bot to tgbot; -- разрешаем запуск любых хранимых процедур в схеме bot,
-- других прав не даем!
go;Следующий этап - создадим слой хранимых процедур, к которым будет обращаться бот для обмена данными. Слой будет содержать 3 хранимые процедуры - bot.get_or_create_user будет вызываться при регистрации пользователя и возвращать recordset, bot.create_issue будет создавать новую поломку или имитировать назначение ответственного (при повторном вызове того же пользователя) и возвращать recordset, bot.close_all_issues будет закрывать все поломки пользователя и возвращать число закрытых событий
-- Запоминаем в базе данных пользователя ТГ
create procedure bot.get_or_create_user @user_tg_id bigint, -- ИД пользователя ТГ
@user_name varchar(128) -- имя пользователя ТГ
as
begin
set nocount on
if not exists(select 1 from bot_user where tg_id = @user_tg_id) -- если нет такого ID - создаем запись
insert into bot_user(tg_id, user_name, f_bot_user_type)
values (@user_tg_id, @user_name, 1); -- по умолчанию ставим роль Рабочий
select bu.id, bu.tg_id, bu.user_name, ut.user_type
from bot_user bu
join dbo.bot_user_type ut on ut.id = bu.f_bot_user_type
where bu.tg_id = @user_tg_id;
end;
go;
-- Создаем новую поломку, если есть незакрытая для данного пользователя и данного типа - не создаем
create procedure bot.create_issue @user_tg_id bigint, -- ИД пользователя ТГ
@issue_type int -- тип поломки, должен быть 2 или 3
as
begin
set nocount on
if @issue_type not in (2, 3)
throw 50011, N'Неизвестный тип поломки', 1;
declare @last_issue int;
select top 1 @last_issue = i.id -- ищем последнюю открытую поломку у этого пользователя
from issue i
join dbo.bot_user b on i.f_bot_user = b.id
where b.tg_id = @user_tg_id
and i.f_bot_user_type = @issue_type
and closed is null
order by i.id desc
if @@rowcount = 0 -- не нашли
begin
insert into issue(f_bot_user, created, f_bot_user_type)
select u.id, getdate(), @issue_type
from bot_user u
where u.tg_id = @user_tg_id
set @last_issue = scope_identity()
end
else -- есть такая поломка, сымитируем назначение ответственного
begin
update issue
set f_bot_user_resp = (select top 1 id
from bot_user
where bot_user.f_bot_user_type = @issue_type
order by id desc) -- выбираем последнего зарегистрированного механика или электрика
where id = @last_issue
end;
-- Наконец, возвращаем результат
select i.id, i.created, bu.user_name, but.user_type, coalesce(resp.user_name, N'Не назначено') as responsible
from issue i
join dbo.bot_user bu on i.f_bot_user = bu.id
join dbo.bot_user_type but on i.f_bot_user_type = but.id
left join dbo.bot_user resp on resp.id = i.f_bot_user_resp
where i.id = @last_issue
end;
go;
-- Закрываем все открытые поломки для пользователя
create procedure bot.close_all_issues
@user_tg_id bigint
as
begin
set nocount on
update i set i.closed = getdate()
from issue i
join dbo.bot_user bu on i.f_bot_user = bu.id
where bu.tg_id = @user_tg_id and i.closed is null
select @@rowcount -- возвращаем число закрытых поломок
end;
go;Телеграм-бот
Цель статьи - продемонстрировать рабочую связку бота и MS SQL Server, поэтому бот будет примитивный. Задачи бота:
По команде /start зарегистрировать пользователя в БД
По команде /issue спросить причину (механика или электрика) и создать инцидент, при повторном создании инцидента с той же причиной имитировать назначение ответственного сотрудника.
По команде /close закрываем все открытые инциденты для данного инициатора
Создаем бота и получаем токен как описано выше, https://habr.com/ru/companies/amvera/articles/820527/
Постарался уложить код проекта в минимум файлов. Получилось 2:
# bot.py - все, что связано с ботом
import asyncio
import logging
from re import Match
import os
from aiogram import Bot, F, Dispatcher, Router
from aiogram.client.default import DefaultBotProperties
from aiogram.enums import ParseMode
from aiogram.filters import Command
from aiogram.fsm.storage.memory import MemoryStorage
from aiogram.types import CallbackQuery, InlineKeyboardButton, InlineKeyboardMarkup, Message
from aiogram.types.bot_command import BotCommand
from dotenv import load_dotenv
from db import DbConn
# Для вывода логов
LOG = logging.getLogger(__name__)
# Читаем .env файл в переменные среды
dotenv_path = os.path.join(os.path.dirname(__file__), '.env')
load_dotenv(dotenv_path)
# Установки программы
BOT_TOKEN = os.getenv("BOT_TOKEN")
SERVICE_CHAT_ID = os.getenv("SERVICE_CHAT_ID")
# Создаем объекты бота
bot = Bot(token=BOT_TOKEN, default=DefaultBotProperties(
parse_mode=ParseMode.HTML))
dp = Dispatcher(storage=MemoryStorage())
router = Router()
# Онлайн-клавиатура при вводе инцидента
def get_inline_keyboard() -> InlineKeyboardMarkup:
keyboard_list = [
[
InlineKeyboardButton(text="Механика", callback_data="issue#2"),
InlineKeyboardButton(text="Электрика", callback_data="issue#3"),
],
[
InlineKeyboardButton(text="Отмена", callback_data="issue#0"),
]
]
return InlineKeyboardMarkup(inline_keyboard=keyboard_list)
# Команды бота
@router.message(Command("start"))
async def start_command(message: Message):
user_info = DbConn.get_or_create_user(
message.from_user.id, message.from_user.full_name)
if isinstance(user_info, dict):
await message.answer(f" Интересные статьи
Интересные статьи
