Перейти к содержанию

Чат-бот Telegram (публикация)

Чат-бот для интеграции с Telegram настраивается с помощью публикации. Публикация получает обновления из Telegram и пишет комментарии в нужную задачу или чат "Первой Формы".

Внешний пользователь использует Telegram и создает в нем чат для общения с сотрудником "Первой Формы". Сотрудник создает задачу в "Первой Форме", чтобы вести переписку и хранить в системе историю сообщений. Для интеграции между чатом Telegram и задачей в "Первой Форме" используется чат-бот: его нужно добавить как участника в чат Telegram и в задачу в "Первой Форме", и тогда он будет дублировать сообщения между двумя этими каналами. Сотрудник "Первой Формы" может отправлять сообщения как из "Первой Формы", так и из чата Telegram.

Таким образом:

  • Все пользователи используют для общения удобный и доступный им канал.

  • В системе сохраняется история переписки в привязке к конкретной обсуждаемой задаче.

Аналогичным способом можно настроить не только дублирование сообщений, но и, например, постановку задач в "Первую Форму" путем отправки чат-боту соответствующего сообщения.

При публикации сообщения делается попытка распознать автора. Если в системе есть учетная запись пользователя с таким ID в Telegram, то сообщения будут публиковаться в "Первой Форме" от имени конкретного пользователя. Если пользователь не указал ID и в системе нет подходящей учетной записи, то в зависимости от содержания публикации, будет либо создан новый пользователь, либо сообщения будут публиковаться от имени служебного пользователя, назначенного на роль чат-бота.

Порядок действий

В Telegram

1. Если вы еще не зарегистрированы в Telegram, пройдите регистрацию.

2. Создайте чат-бота. Для получения идентификатора нового бота (bot token) найдите в Telegram пользователя \@BotFather, запустите с ним чат (команда /start при первом обращении) и введите команду создания нового бота: /newbot.

Введите публичное имя для вашего бота и имя пользователя для бота в Telegram. Обратите внимание: имя пользователя должно быть уникальным и должно заканчиваться на "bot" (например: Chat1FBot). Если пользователь с таким именем уже существует вы увидите ошибку: "Sorry, this username is invalid" — необходимо ввести уникальное имя. При успешном создании вы получите сообщение с ссылкой на свого бота в Telegram и токеном для доступа к HTTP API. ID бота потребуется подставить вместо tlgChatBot, а токен — вместо tlgToken в Lua скрипте.

3. Добавьте созданного чат-бота в качестве участника в нужный чат.

В "Первой Форме"

1. Создайте служебного пользователя, который будет выполнять роль чат-бота. В скрипте ниже подставьте его ID вместо 1fChatUserID.

2. Создайте таблицы в базе данных:

  • Таблица для хранения списка чатов в Telegram и соответствующих задач в "Первой Форме".

  • Таблица для для хранения ID всех сообщений из Telegram и соответствующих им комментариев в "Первой Форме".

Скрипт создания таблиц:

--1. cm_Telegram_ChatToTask
--Таблица для хранения списка чатов в ТГ и соответствующих задач в 1ф
--TaskID - задача в 1ф
--ChatID- чат в ТГ

CREATE TABLE [dbo].[cm_Telegram_ChatToTask](
 [TaskID] [int] NOT NULL,
 [ChatID] varchar NOT NULL,
CONSTRAINT [PK__dbo__cm_Telegram_ChatToTask] PRIMARY KEY CLUSTERED
(
 [TaskID] ASC,
 [ChatID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[cm_Telegram_ChatToTask]  WITH CHECK ADD  CONSTRAINT [FK__dbo__cm_Telegram_ChatToTask__TaskID] FOREIGN KEY([TaskID])
REFERENCES [dbo].[Tasks] ([TaskID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[cm_Telegram_ChatToTask] CHECK CONSTRAINT [FK__dbo__cm_Telegram_ChatToTask__TaskID]
GO

--2. cm_tlg_tasks Табла для хранения ID всех сообщений из ТГ и соответсвующих им комментариев в 1ф
--tlg_chat_id - Чат в ТГ
--tlg_id - ID сообщения в ТГ
--task_id - номер коментария в 1Ф

CREATE TABLE [dbo].[cm_tlg_tasks](
 [tlg_chat_id] varchar NULL,
 [tlg_id] [int] NULL,
 [task_id] [int] NULL
) ON [PRIMARY]
GO

ℹ️ Таблицы должны быть созданы перед настройкой публикации

Когда пользователь пишет из Telegram, публикация делает запись в таблицу, которая включает все задачи, связанные с Telegram. После того, как пользователь отправляет комментарий, "Первая Форма" проверяет, входит ли эта задача в список синхронизации с Telegram. Если входит — производит действия для отправки этого комментария в Telegram. Перед завершением работы публикация удаляет подписчиков из чата Telegram.

После каждого отправленного или полученного сообщения в таблицу БД записывается: номер чата в Telegram, номер сообщения из Telegram, ID сообщения из "Первой Формы."

3. При отправке сообщения из Telegram в "Первую Форму" и наоборот, формируется JSON с набором параметров. "Первая Форма" обрабатывает этот JSON и берет оттуда нужные данные, например: имя пользователя, имя группы, текст сообщения и т. п. Для обработки JSON используются публикации.

Создайте и сделайте активной публикацию, которая будет дублировать сообщения (или выполнять другие нужные действия). Настройте доступ к публикации — включите параметры Виден всем и Включить анонимный доступ.

Для входящих сообщений из Telegram

Публикация

Публикация имеет тип POST и единственный входящий параметр requestBody.

Пример настроенной публикации.

Пакет действий для публикации содержит три смарта:

1. HTTP ответ

Параметр Значение Тип возвращаемого результата JSON Тело ответа {"ok":200} Код ответа OK Заголовки - Файл Оставить пустым

2. SQL-скрипт (опционально, действие не является обязательным).

Параметр Значение
SQL скрипт [dbo].[1fSynkChat_webhook_telegram] @Mode = @json

3. Выполнить Lua скрипт

Параметр Значение
Lua скрипт Отправить комментарий в 1Ф чат 
--формирование имени пользователя
local getFirstLastName = function(fName,lName,uName)
  local res = ""

  if fName ~= nil and lName ~= nil then
     res = fName.." "..lName
  else
     if fName ~= nil then
        res = fName
     else
        res = lName
     end
  end

  if uName ~= nil then
     if res ~= nil and res ~= "" then
        res = res.." @"..uName
     else
        res = "@"..uName
     end
  end

  return res
end

--запрос в БД, возвращает скалярное значение либо nil
local getSQLScalarValue = function(query,params,varName)
  local res

  if query == '' or query == nil then
     res = nil
     return res
  end

  if varName == '' or varName == nil then
     res = nil
     return res
  end

  res = SQL:query(query,params)

  if res[1] == nil then
     res = nil
     return res
  else
     res = res[1][varName]
     return res
  end
end

--найти пользователя 1Формы по его нику в Telegram
local getUserIdByTelegramId = function(tlgUserID)
  local res = getSQLScalarValue(
     [[
     ;with cte as (
        select cast(@TlgUserID as varchar(max)) TlgUserID
        )
        select isnull(u.UserID,-1) fromUserID
        from cte
           left join Users u on u.ICQ = cte.TlgUserID
     ]],
     {
        TlgUserID = tlgUserID
     },
     "fromUserID"
  )

  return res
end

--Удалить из чата 1ф
local removeSubscriber = function(chatTaskID,userID,delUserID,commentText)
  local doNotWriteComment = false
  if commentText == nil or commentText == '' then
     doNotWriteComment = true
  end

  SMART:execute_action("RemoveSubscriber", chatTaskID, "task",
     {
        Task = chatTaskID,
        DeletingUser = userID,
        UserToDelete = delUserID,
        Reason = commentText,
        DoNotWriteComment = doNotWriteComment
     }
  )
end

--добавить в подписчики, если не подписан
local addToSubscribers = function(sysUserID,taskID,userID)
  local SubscriberIsExists

  SubscriberIsExists = getSQLScalarValue(
     [[
        select 1 IsExists
        from dbo.fn_cm_GetSubScribersOnTask(@chatTaskID)
        where UserID = @fromUserID
     ]],
     {
        chatTaskID = taskID,
        fromUserID = userID
     },
     "IsExists"
  )

  --если не подписан к чату
  if SubscriberIsExists ~= 1 then
     --добавить в подписчики
     SMART:execute_action("AddSubscriber", taskID, "task", {
        Who = sysUserID, --кто добавляет
        Whom = userID, --кого добавить
        Task = taskID, --куда добавить
        DoNotWriteComment = true --не писать комментарий
     })
  end

  return
end

local addComment = function(taskID,userID,commentText,isQuestion)
  SMART:execute_action("PostComment", taskID, "task",
  {
     CommentAuthor = userID,
     CommentText = commentText,
     Recipients = nil,
     RecipCopies = nil,
     CommentType = 3,
     Task = taskID,
     ForcedEmail = false,
     CommentSMS = false,
     TextAsHTML = false,
     NoSubscription = false,
     MarkAsQuestion = isQuestion,
     CommentVisibleOnlyToRealRecipients = false
  })
end

local addFile = function(taskID,userID,link,commentText,fileName)
  SMART:execute_action("DownloadFile", taskID, "task",
  {
     Task = taskID,
     UploadingUserName = userID,
    TargetExternalParameter = nil,
    FileLink = link,
     Comment = commentText,
    DownloadManyFiles = false,
    FileMask = nil,
    FileCreateDateFrom = nil,
    FileCreateDateTo = nil,
     FileName = fileName
  })
end

local createChatTask = function(ChatName,tlgChatID,UserIDs)
  local res = SMART:execute_action("CreateTask", nil, "task",
  {
     Owner = systemUserID,
     Subcat = systemChatSubcatID,   
     TaskText = ChatName,
     CreateLink = false,
     CreateSubtask = false,
     Performers = nil,
     DueTime = nil,
     TaskStartTime = nil,
     ExtParams = nil,
     NewTaskCopySubscribers = false,
     CreateCopyFiles = false,
     CopyParentText = false,
     Priority = 1,
     Notify = nil,
     UsersToSubscribe = UserIDs,
     CreateCopiesForEachPerformer = false,
     LinkFiles = nil,
     AssignLetterWithTask = nil,
     Confidentiality = nil,
     LinkAsUser = nil
  })

  if res ~= nil then
     SQL:query(
     [[
        insert cm_Telegram_ChatToTask
        select cast(@tlgChatID as varchar(20)), @TaskChatID
     ]],
     {
        tlgChatID = tlgChatID,
        TaskChatID = res[0]
     }
     )
  end

  return res
end

systemUserID = getSQLScalarValue("select top 1 SystemRobotID from Settings where CustomerID = 1",{},"SystemRobotID")   --Системный пользователь
systemChatSubcatID = getSQLScalarValue("select top 1 ChatSubcatID from settings",{},"ChatSubcatID") --Категория чатов

local bot = {}
bot.id = "tlgChatBot"
bot.token = "tlgToken"
bot.host = "api.telegram.org"
bot.getURL = function(methodName)
  local res
  local h = "https://"

  if methodName == nil or methodName == "" then
     return nil
  end

  local case = {
     ["getFile"] = function()
        local resCase = h..bot.host.."/bot"..bot.id..":"..bot.token.."/getFile"
        return resCase
     end,
     ["getMe"] = function()
        local resCase = h..bot.host.."/bot"..bot.id..":"..bot.token.."/getMe"
        return resCase
     end,
     ["file"] = function()
        local resCase = h..bot.host.."/file/bot"..bot.id..":"..bot.token
        return resCase
     end
  }

  res = case[methodName]()
  return res
end

local getFile = function(fileId)
  local res = nil

  local reqParams = {}
  reqParams.method = "GET"
  reqParams.url = bot.getURL("getFile")
  reqParams.parameters = {file_id = fileId}
  reqParams.headers = {ContentType = "application/json"}
  reqParams.rawBody = {}

  local req = HTTP:send_http_request(
     reqParams.method,
     reqParams.url,
     reqParams.parameters,
     reqParams.headers,
     UTILS:json_encode(reqParams.rawBody)
  )

  local response = UTILS:json_decode(req["HttpResponse"]["ResponseContent"])
  if response["ok"] then
     local file = {}
     file.url = bot.getURL("file")
     file.path = response["result"]["file_path"]
     res = file.url.."/"..file.path
  end

  return res
end

local params = UTILS:json_decode(EVENTPARAMS["PublishedObjectParameters"])

local chatBotUserID = 1fChatUserID     --UserId бота, от которого приходят сообщения, если пользователь не идентифицирован в 1f

local tlgUserID = params["requestBody"]["message"]["from"]["id"]   --id пользователя в telegram
local tlgReplyUserID
if params["requestBody"]["message"]["reply_to_message"] ~= nil then
  tlgReplyUserID = params["requestBody"]["message"]["reply_to_message"]["from"]["id"] --id пользователя кому отвечает автор
end

local tlgCaption = params["requestBody"]["message"]["caption"] --Подпись к файлу, документу
local tlgFileId = getSQLScalarValue("select top 1 json_value([value],'$.file_id') FileID from openjson(@json,'$.requestBody.message.photo') order by iif([key] = 0,'10',[key])",{json = EVENTPARAMS["PublishedObjectParameters"]},"FileID")
local tlgFileName

if tlgFileId == nil then
  tlgFileId = params["requestBody"]["message"]["document"]
  if tlgFileId ~= nil then
     tlgFileId = params["requestBody"]["message"]["document"]["file_id"]
     tlgFileName = params["requestBody"]["message"]["document"]["file_name"]
  end
end

local tlgChatId = params["requestBody"]["message"]["chat"]["id"]   --id группы в telegram
local tlgChatName = params["requestBody"]["message"]["chat"]["title"] --Название группы в telegram
local tlgMessageText = params["requestBody"]["message"]["text"] --текст сообщения
if tlgMessageText == '' then
  tlgMessageText = nil
end

local isQuestion = false
local tlgAddUserID = nil --UserID пользователя, которого добавили в чата
local tlgDelUserID = nil --UserID пользователя, которого удалили из чата

local leftMemberId = nil
if params["requestBody"]["message"]["left_chat_participant"] ~= nil then
  leftMemberId = params["requestBody"]["message"]["left_chat_participant"]["id"]
end

if params["requestBody"]["message"]["left_chat_member"] ~= nil then
  leftMemberId = params["requestBody"]["message"]["left_chat_member"]["id"]
end

if leftMemberId ~= nil then
  tlgDelUserID = getSQLScalarValue(
     [[
        ;with cte as (
           select cast(@TlgUserID as varchar(max)) TlgUserID
        )
        select isnull(u.UserID,-1) LeftUserID
        from cte
        left join Users u on u.ICQ = cte.TlgUserID
     ]],
     {
        TlgUserID = leftMemberId
     },
     "LeftUserID"
  )
  if tlgDelUserID == -1 then
     tlgDelUserID = nil
  end
end

--Имя в telegram
local tlgFirstLastName = getFirstLastName( 
  params["requestBody"]["message"]["from"]["first_name"],
  params["requestBody"]["message"]["from"]["last_name"],
  params["requestBody"]["message"]["from"]["username"] --Ник в telegram
)

--UserID в 1f, от имени которого будет сформировано сообщение в 1f чатах
local fromUserID = getSQLScalarValue(
  [[
     ;with cte as (
        select cast(@TlgUserID as varchar(max)) TlgUserID
     )
     select isnull(u.UserID,@ChatBotUserID) fromUserID
     from cte
     left join Users u on u.ICQ = cte.TlgUserID
  ]],
  {
     TlgUserID = tlgUserID,
     ChatBotUserID = chatBotUserID
  },
  "fromUserID"
)

if fromUserID == chatBotUserID then
  if tlgMessageText ~= nil then
     tlgMessageText = "["..tlgFirstLastName.." ("..tlgUserID..")"..": "..tlgMessageText
  end
end

--Задача чата в 1f

local chatTaskID = getSQLScalarValue(
  [[
     select TaskID
     from cm_vw_ChatToTask
     where ChatID = @tlgChatId
  ]],
  {tlgChatId = tlgChatId},
  "TaskID"
)

--если задачу чата не нашли, то создадим новую задачу
if chatTaskID == nil then
  local res = createChatTask(tlgChatName,tlgChatId,{fromUserID})
  if res ~= nil then
     chatTaskID = res[0]
  end
else
  if tlgDelUserID ~= nil then
     removeSubscriber(chatTaskID,fromUserID,tlgDelUserID)
  end
end

local addMemberId = nil
if params["requestBody"]["message"]["new_chat_member"] ~= nil then
  addMemberId = params["requestBody"]["message"]["new_chat_member"]["id"]
end

if params["requestBody"]["message"]["new_chat_participant"] ~= nil then
  addMemberId = params["requestBody"]["message"]["new_chat_participant"]["id"]
end

if addMemberId ~= nil then
  tlgAddUserID = getUserIdByTelegramId(addMemberId)
  if tlgAddUserID > 0 then
     addToSubscribers(systemUserID,chatTaskID,tlgAddUserID)
  end
end

--есть кто отправляет и есть что отправить
if fromUserID ~= nil then
  --подписываем пользователя к задаче (к чату) если он не подписан
  addToSubscribers(systemUserID,chatTaskID,fromUserID)

  --входящий JSON
  if chatTaskID == defchatTaskID then
     addComment(chatTaskID,fromUserID,EVENTPARAMS["PublishedObjectParameters"],isQuestion)
  end

  if tlgMessageText ~= nil then
     --отправляем сообщение 
     addComment(chatTaskID,fromUserID,tlgMessageText,isQuestion)
  end

  if tlgFileId ~= nil then
     local filePath = getFile(tlgFileId)
     addFile(chatTaskID,fromUserID,filePath,"testComment",tlgFileName)
     if tlgCaption ~= nil then
        addComment(chatTaskID,fromUserID,tlgCaption,isQuestion)
     end
  end
end

RESULT = true;

В Lua-скрипте должны быть обозначены все функции, которые будут использоваться. Переменные, которые будет использовать "Первая Форма":

  • telegram message

  • telegram message id

  • telegram user id

  • telegram username

  • replied message (если есть ответ на сообщение)

Публикация достает все нужные параметры из JSON и заполняет ими сообщение из Telegram. Далее публикация разбирает часть скрипта, где можно увидеть: где найти сообщение, пользователя и так далее. Публикация находит в JSON нужного пользователя в "Первой Форме", нужную задачу, текст, который пользователь написал, отправленный файл, получателя.

Для исходящих сообщений в Telegram

Для общения с созданным смарт-ботом необходимо отправить HTTP-запрос. В "Первой Форме" в разделе Системный есть две категории: Общение и Группы. В этих категориях находятся задачи с чатами для Telegram. В категории Общение находятся личные чаты, в которые могут быть добавлены только два участника с учетом чат-бота. Групповые чаты находятся в категории Группы.

Обе категории обрабатываются смарт-автоматизацией для корректной работы.

Пользователь "Первой Формы" может отправить чат-боту обычный комментарий, а может комментарий-ответ. Если в комментарии указано, что это ответ пользователю, то в телеграмме он также будет отображаться, как ответ.

Когда "Первая Форма" хочет что-то отправить телеграм-боту в Telegram, она выполняет этот HTTP-запрос. Желаемые параметры (номер чата, текст, получатели) нужно указать в списке параметров.

ℹ️ Бот — один на всю площадку, при этом допустимо создание любого количества групповых чатов

Создайте HTTP-запрос и в параметр Url в качестве адреса вставьте токен, полученный от чат-бота: https://api.telegram.org/bot716568949:AAE4xKdeM4KFkXorZa8Dag4uOD7hUcTLqaM/sendMessage

Примеры настройки смарт-правил в системной категории

Удаление пользователя из чата Telegram после удаления подписчика из задачи:

Событие: После удаления подписчика Создано смарт-правило Smart фильтр: Задача чата входит в список синхронизации с телеграмм

select 1
from cm_vw_ChatToTask v
where v.TaskID = @ContextID

Смарт-действие: Отправить HTTP запрос Смарт-действие "Отправить HTTP запрос" Тип HTTP запроса: Get Url: токен, полученный от чат-бота Список параметров: -chat_id — определение ChatID группы Telegram

SELECT chatid
FROM   cm_vw_chattotask
WHERE  taskid = @ContextID

Определение ChatID группы Telegram -user_id — TelegramID пользователя, которого удалили из подписчиков

select ICQ
from Users with(nolock)
where UserID = @eventParam0

TelegramID пользователя, которого удалили из подписчиков

Обновление сообщения в Telegram после редактирования комментария в системе:

Событие: После редактирования комментария Создано смарт-правило Smart фильтр: Задача чата входит в список синхронизации с Telegram и номер комментария есть в списке синхронизации

select 1
from cm_vw_ChatToTask v
join [custom].[cm_tlg_tasks] t
on v.chatid=t.tlg_chat_id
where v.TaskID =  @ContextID
and t.task_id = @eventparam0

Смарт-действие: Отправить HTTP запрос Смарт-действие "Отправить HTTP запрос" Тип HTTP запроса: Post Url: токен, полученный от чат-бота Список параметров: -chat_id — определение ChatID Telegram по номеру задачи

declare @ChatID varchar(max) = null

select @ChatID = ChatID
from cm_vw_ChatToTask
where TaskID = @ContextID

select isnull(@ChatID,'') ResTxt

Определение ChatID группы Telegram по номеру задачи -message_id — MessageID Telegram по номеру комментария

select tlg_id
from cm_vw_ChatToTask v
join [custom].[cm_tlg_tasks] t
on v.chatid=t.tlg_chat_id
where v.TaskID = @ContextID
and t.task_id = @eventparam0

MessageID Telegram по номеру комментария -text — Новый текст комментария после редактирования Новый текст комментария после редактирования -entities — Адресаты комментария после редактирования

declare @json nvarchar(max)
set @json = (
   SELECT sum([pre_offset])over(order by [name]) as [offset],[length],[type], JSON_QUERY([user]) as [user]
   FROM
   (
       select lag(len(isnull(u.TelegramUserName,u.FirstName)),1,-2)over(order by isnull(u.TelegramUserName,u.FirstName))+2 as [pre_offset]
             ,len(isnull(u.TelegramUserName,u.FirstName))+1 as [length]
             ,iif(u.TelegramUserName is null, N'text_mention', N'mention') as [type]
             ,isnull(u.TelegramUserName,u.FirstName) as [name]
             ,iif(u.TelegramUserName is null,JSON_QUERY(ca.v),null) as [user]
       from CommentRecipients cp
       join users u (nolock)
           on u.UserID = cp.UserID
       CROSS APPLY (SELECT ICQ as [id]
                          ,cast(0 as bit) as [is_bot]
                          ,FirstName as [first_name]
                          ,LastName as [last_name]
                    FROM Users with(nolock)
                    WHERE UserID= u.UserID
                    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
       ) as ca(v)
       where CommentID = @eventParam0 and isnull(nullif(u.TelegramUserName,''),nullif(u.ICQ,'')) is not null
       and cp.IsRealRecipient = 1

   ) tab
   FOR JSON AUTO
)

select isnull(@json,'[]')

Адресаты комментария после редактирования

Отправка ответа боту в Telegram после написания комментария в системе:

Событие: После написания комментария Создано смарт-правило Smart фильтр: Задача чата входит в список синхронизации с Telegram, комментарий не пустой, не является ответом, содержит адресатов и его написал не робот

declare
 @all int,        --все получатели
 @real int        --кому адресовано

select
 @all = count(iif(u.IsFired_2 = 0,cr.UserID,null)),
 @real = count(iif(IsRealRecipient = 1,cr.UserID,null))
from CommentRecipients cr
join Users u on u.UserID = cr.UserID
where commentid = @eventParam0

select 1
from Comments (nolock) c
where c.CommentID = @eventParam0
and c.TaskID = @ContextID
and c.RealUserID is null
and @CurrentSessionUserID not in (-1,3,8330)
and exists(
 select 1
   from cm_vw_ChatToTask v
   where v.TaskID = c.TaskID
)
and len(@eventParam1) != 0
and @real < (@all - 1)
and InReplyToCommentID is null

Два смарт-действия:

1. Отправить HTTP запрос Смарт-действие "Отправить HTTP запрос" Тип HTTP запроса: Post Url: токен, полученный от чат-бота Список параметров: -chat_id — ChatID Telegram по номеру задачи чата в системе

declare @ChatID varchar(max) = null

select @ChatID = ChatID
from cm_vw_ChatToTask
where TaskID = @ContextID

select isnull(@ChatID,'') ResTxt

ChatID Telegram по номеру задачи чата в системе -text — Текст комментария plaintext с адресатами

SELECT '['
+ u.DisplayName + ': '+
u_cr.TelegramUserName
+ ' ' +  @EventParam1
FROM Comments as c
join users u
on c.userid = u.userid
join (
SELECT  CommentID,
STUFF((SELECT ' ' +
case when left(u_cr.TelegramUserName,1) = '@'
then u_cr.TelegramUserName
else '@'+ u_cr.TelegramUserName
end
from CommentRecipients t2
join users u_cr
on t2.userid = u_cr.userid
WHERE t1.CommentID = t2.CommentID and IsRealRecipient = 1 FOR XML PATH('')),1,1,'') TelegramUserName
from CommentRecipients t1
where t1.CommentID = @EventParam0
GROUP BY CommentID
)u_cr
on  u_cr.CommentID  = c.CommentID
WHERE c.CommentID=@EventParam0

Текст комментария plaintext -entities — Номер комментария, на который ответ из Telegram

declare @json nvarchar(max)
set @json = (
   SELECT sum([pre_offset])over(order by [name]) as [offset],[length],[type], JSON_QUERY([user]) as [user]
   FROM
   (
       select lag(len(isnull(u.TelegramUserName,u.FirstName)),1,-2)over(order by isnull(u.TelegramUserName,u.FirstName))+2 as [pre_offset]
             ,len(isnull(u.TelegramUserName,u.FirstName))+1 as [length]
             ,iif(u.TelegramUserName is null, N'text_mention', N'mention') as [type]
             ,isnull(u.TelegramUserName,u.FirstName) as [name]
             ,iif(u.TelegramUserName is null,JSON_QUERY(ca.v),null) as [user]
       from CommentRecipients cp
       join users u (nolock)
           on u.UserID = cp.UserID
       CROSS APPLY (SELECT ICQ as [id]
                          ,cast(0 as bit) as [is_bot]
                          ,FirstName as [first_name]
                          ,LastName as [last_name]
                    FROM Users with(nolock)
                    WHERE UserID= u.UserID
                    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
       ) as ca(v)
       where CommentID = @eventParam0 and isnull(nullif(u.TelegramUserName,''),nullif(u.ICQ,'')) is not null
       and cp.IsRealRecipient = 1

   ) tab
   FOR JSON AUTO
)

select isnull(@json,'[]')

Адресаты комментария

2. Выполнить sql скрипт Смарт-действие "Выполнить sql скрипт" Sql скрипт — Запись результата в таблицу

insert into [custom].[cm_tlg_tasks] --таблица синхронизации сообщений ТГ и 1Ф
           select cast(JSON_VALUE(@ActionResult34071, '$.HttpResponse.ResponseContent.result.chat.id') as varchar(20)),
           cast(JSON_VALUE(@ActionResult34071, '$.HttpResponse.ResponseContent.result.message_id') as varchar(20)),
           cast(@eventParam0 as varchar(20))

Sql скрипт

Отправка ответа в Telegram на всех после написания комментария в системе:

Событие: После написания комментария Создано смарт-правило Smart фильтр: Задача чата входит в список синхронизации с Telegram, комментарий не пустой, является ответом и его написал не робот

declare
 @all int,        --все получатели
 @real int        --кому адресовано

select
 @all = count(iif(u.IsFired_2 = 0,cr.UserID,null)),
 @real = count(iif(IsRealRecipient = 1,cr.UserID,null))
from CommentRecipients cr
join Users u on u.UserID = cr.UserID
where commentid = @eventParam0

select 1
from Comments (nolock) c
where c.CommentID = @eventParam0
and c.TaskID = @ContextID
and (@CurrentSessionUserID not in (-1,3,8330) or c.RealUserID is not null)
and exists(
 select 1
   from cm_vw_ChatToTask v
   where v.TaskID = c.TaskID
)
and len(@eventParam1) != 0
and @real = (@all - 1)
and InReplyToCommentID is not null

Два смарт-действия:

1. Отправить HTTP запрос Смарт-действие "Отправить HTTP запрос" Тип HTTP запроса: Post Url: токен, полученный от чат-бота Список параметров: -chat_id — определение ChatID Telegram по номеру задачи

declare @ChatID varchar(max) = null

select @ChatID = ChatID
from cm_vw_ChatToTask
where TaskID = @ContextID

select isnull(@ChatID,'') ResTxt

Определение ChatID группы Telegram по номеру задачи -text — Текст комментария plaintext Текст комментария plaintext -reply_to_message_id — Номер комментария, на который ответ из Telegram

select
case when tlg_id is not null then tlg_id else 0 end
from comments c
left join  [custom].[cm_tlg_tasks] t
on t.task_id = c.InReplyToCommentID
where c.commentid =  @EventParam0

Номер комментария, на который ответ из Telegram

2. Выполнить sql скрипт Смарт-действие "Выполнить sql скрипт" Sql скрипт — Запись результата в таблицу

insert into [custom].[cm_tlg_tasks]
           select cast(JSON_VALUE(@ActionResult74280, '$.HttpResponse.ResponseContent.result.chat.id') as varchar(20)),
           cast(JSON_VALUE(@ActionResult74280, '$.HttpResponse.ResponseContent.result.message_id') as varchar(20)),
           cast(@eventParam0 as varchar(20))

Sql скрипт

Отправка сообщения в Telegram на всех после написания комментария в системе:

Событие: После написания комментария Создано смарт-правило Smart фильтр: Задача чата входит в список синхронизации с Telegram, комментарий не пустой, не является ответом и его написал не робот

declare
 @all int,        --все получатели
 @real int        --кому адресовано

select
 @all = count(iif(u.IsFired_2 = 0,cr.UserID,null)),
 @real = count(iif(IsRealRecipient = 1,cr.UserID,null))
from CommentRecipients cr
join Users u on u.UserID = cr.UserID
where commentid = @eventParam0

select 1
from Comments (nolock) c
where c.CommentID = @eventParam0
and c.TaskID = @ContextID
and (@CurrentSessionUserID not in (-1,3,8330) or c.RealUserID is not null)
and exists(
 select 1
   from cm_vw_ChatToTask v
   where v.TaskID = c.TaskID
)
and len(@eventParam1) != 0
and @real = (@all - 1)
and InReplyToCommentID is null

Два смарт-действия:

1. Отправить HTTP запрос Смарт-действие "Отправить HTTP запрос" Тип HTTP запроса: Post Url: токен, полученный от чат-бота Список параметров: -chat_id — определение ChatID Telegram по номеру задачи

declare @ChatID varchar(max) = null

select @ChatID = ChatID
from cm_vw_ChatToTask
where TaskID = @ContextID

select isnull(@ChatID,'') ResTxt

Определение ChatID группы Telegram по номеру задачи -text — Текст комментария plaintext Текст комментария plaintext

2. Выполнить sql скрипт Смарт-действие "Выполнить sql скрипт" Sql скрипт — Запись результата в таблицу

insert into [custom].[cm_tlg_tasks]
           select cast(JSON_VALUE(@ActionResult74280, '$.HttpResponse.ResponseContent.result.chat.id') as varchar(20)),
           cast(JSON_VALUE(@ActionResult74280, '$.HttpResponse.ResponseContent.result.message_id') as varchar(20)),
           cast(@eventParam0 as varchar(20))

Sql скрипт

Удаление сообщения в чате Telegram перед удалением комментария в задаче:

Событие: Перед удалением комментария Создано смарт-правило Smart фильтр: Задача чата входит в список синхронизации с Telegram

select 1
from cm_vw_ChatToTask v
where v.TaskID = @ContextID

Смарт-действие: Отправить HTTP запрос Смарт-действие "Отправить HTTP запрос" Тип HTTP запроса: Post Url: токен, полученный от чат-бота Список параметров: -chat_id — определение ChatID Telegram по номеру задачи

declare @ChatID varchar(max) = null

select @ChatID = ChatID
from cm_vw_ChatToTask
where TaskID = @ContextID

select isnull(@ChatID,'') ResTxt

Определение ChatID группы Telegram по номеру задачи -message_id — Сообщение, которое удаляется

declare @ChatID varchar(max) = null

select tlg_id
from [custom].[cm_tlg_tasks]
where tlg_chat_id =
(
 select ChatID
 from cm_vw_ChatToTask
 where TaskID = @ContextID
)
and
task_id = @eventParam0

Сообщение, которое удаляется

Автопрочтение комментария после его написания:

Событие: После написания комментария Создано смарт-правило Smart фильтр: Задача чата входит в список синхронизации с Telegram

select 1
from cm_vw_ChatToTask v
where v.TaskID = @ContextID

Смарт-действие: Выполнить sql скрипт Смарт-действие "Выполнить sql скрипт" Список параметров: -ID — ID комментария ID комментария Sql скрипт:

update CommentRecipients set IsUnread = 0 where CommentID = @ID

Удаление сообщения в Telegram при удалении задачи в системе:

Событие: Перед удалением задачи Создано смарт-правило Смарт-действие: Выполнить sql скрипт Смарт-действие "Выполнить sql скрипт" Sql скрипт:

local msg_id = SQL:scalar("SELECT TOP 1 ExtParam31991Value FROM TasksInSubcat1644Denormalized WHERE TaskId = @TaskId", {TaskId = CONTEXT['Id']})
local url = 'https://api.telegram.org/bot/deleteMessage';
local body = UTILS:json_encode({
chat_id = ,
message_id = msg_id
})
local headers = {
   ["Content-Type"] = "application/json"
}

local res = HTTP:send_http_request('POST', url, nil, headers, body)

Sql скрипт

Отправить сообщение в Telegram после перехода по маршруту в задаче:

Событие: После перехода Создано смарт-правило Smart фильтр: Нет MessageID Smart фильтр Смарт-действие: Выполнить sql скрипт Смарт-действие "Выполнить sql скрипт" Sql скрипт:

local task_info = SQL:query([[
   SELECT TOP 1 replace(td.Tasktext,'_','\_') AS text, ldov.Value AS theme, td.ExtParam31991Value AS msg_id, u.DisplayName AS user_name
   FROM TasksInSubcat1644Denormalized td with (nolock)
       JOIN Users u with (nolock) ON td.OwnerId = u.UserId
         JOIN dbo.ExtParamValues epv with (nolock) ON epv.TaskID = td.TaskID AND epv.ExtParamID = 5696
         JOIN dbo.LocalizedDataObjectValues ldov with (nolock) on ldov.LocalizationId = epv.LocalizedExtParamValueId
   WHERE td.TaskId = @TaskId
]], {TaskId = CONTEXT['Id']});

local new_employee = SQL:query([[
   SELECT TOP 1 IIF(td.ExtParam5696Value like '%Пополнение в «Первой Форме»%' , 1, 0) as b
   FROM TasksInSubcat1644Denormalized td
       JOIN Users u ON td.OwnerId = u.UserId
   WHERE td.TaskId = @TaskId
]], {TaskId = CONTEXT['Id']});

local url = 'https://api.telegram.org/bot/sendMessage';
local link = '' .. tostring(CONTEXT['Id'])

local text

if new_employee[1].b == 1
then
   local text_m = task_info[1].text:gsub("Добро пожаловать в команду!", "\n\nДобро пожаловать в команду!")
   text = '*' .. task_info[1].theme .. ' \xF0\x9F\x9A\x80' ..  '*\n\n' .. text_m .. '\n\n_' .. task_info[1].user_name  .. '_' .. '\nперейти';
else
   text = '*' .. task_info[1].theme .. '*\n' .. task_info[1].text .. '\n\n_' .. task_info[1].user_name  .. '_' .. '\nперейти';
end

local emojiDecoded = text:gsub("&#(%d+);", function(charCode)
   return utf8.char(tonumber(charCode))
end)

local body = UTILS:json_encode({
   chat_id = '',
   text = emojiDecoded,
   parse_mode = 'Markdown'--'MarkdownV2'--'HTML'
})
local headers = {
   ["Content-Type"] = "application/json"
}
RESULT = body
local res = HTTP:send_http_request('POST', url, nil, headers, body)
local response = UTILS:json_decode(res.HttpResponse.ResponseContent)
--var_dump(response)
local msg_id = response.result.message_id

SMART:execute_action('ChangeExtParamValue', CONTEXT['Id'], 'Task', {
 Task = CONTEXT['Id'],
 User = 3,
 ExtParam = 31991,
 Value = response.result.message_id,
 WriteCommentOnChange = false
})

Sql скрипт

Обновление сообщения в Telegram после смены текста задачи в системе:

Событие: После смены текста задачи Создано смарт-правило Smart фильтр: Есть Telegram MessageID и пользователь, сменивший текст задачи, не робот Smart фильтр Смарт-действие: Выполнить sql скрипт Смарт-действие "Выполнить sql скрипт" Sql скрипт:

local task_info = SQL:query([[
SELECT
 TOP 1 replace(td.Tasktext, '_', '\_') AS text,
 ldov.Value AS theme,
 td.ExtParam31991Value AS msg_id,
 u.DisplayName AS user_name
FROM
 TasksInSubcat1644Denormalized td
 JOIN dbo.Users u ON td.OwnerId = u.UserId
 JOIN dbo.ExtParamValues epv ON epv.TaskID = td.TaskID
 JOIN dbo.LocalizedDataObjectValues ldov on ldov.LocalizationId = epv.LocalizedExtParamValueId
WHERE
 1 = 1
 AND td.TaskId = @TaskId
 AND epv.ExtParamID = 5696
]], {TaskId = CONTEXT['Id']});

local url = 'https://api.telegram.org/bot/sendMessage';
local link = '' .. tostring(CONTEXT['Id'])
local text = '*' .. task_info[1].theme .. '*\n' .. task_info[1].text .. '\n\n_' .. task_info[1].user_name  .. '_' .. '\nперейти';

local emojiDecoded = text:gsub("&#(%d+);", function(charCode)
   return utf8.char(tonumber(charCode))
end)

local body = UTILS:json_encode({
   chat_id = '',
   text = emojiDecoded,
   parse_mode = 'Markdown',--'HTML'
   message_id = task_info[1].msg_id
})
local headers = {
   ["Content-Type"] = "application/json"
}
local res = HTTP:send_http_request('POST', url, nil, headers, body)

RESULT = "success"

Sql скрипт

Особенности настройки синхронизации с Telegram

Администратор создает в Telegram группу, а затем добавляет в нее нужных пользователей и чат-бот. После того, как один из пользователй отправит сообщение в группе Telegram, в "Первой Формы" автоматически будет создан групповой чат. Все сообщения в групповом чате будут будут синхронизироваться с группой в Telegram.

ℹ️ В настройках профилей пользователей в "Первой Форме" обязательно должно быть заполнено поле Telegram ID

Если пользователь не зарегистрирован в "Первой Форме", то после отправки первого сообщения из группы в Telegram, в системе будет создан новый профиль.