SYS_ROBOT = 3
WA_ROBOT = 2349 --пользователь whatsapp_robot
WA_SUBCAT = 117 --категория чатов
CONTACT_SUBCAT = 49 --категория контактных лиц
PARAMS = UTILS:json_decode(EVENTPARAMS["PublishedObjectParameters"]); --json входящих параметров
function createTask (owner, subcat, tasktext, performers, extparams)
local res = SMART:execute_action("CreateTask", nil, "task",
{
Owner = owner,
Subcat = subcat,
TaskText = tasktext,
CreateLink = false,
CreateSubtask = false,
Performers = performers,
DueTime = nil,
TaskStartTime = nil,
ExtParams = extparams,
NewTaskCopySubscribers = false,
CreateCopyFiles = false,
CopyParentText = false,
Priority = 1,
Notify = nil,
UsersToSubscribe = nil,
CreateCopiesForEachPerformer = false,
LinkFiles = nil,
AssignLetterWithTask = nil,
Confidentiality = nil,
LinkAsUser = nil
}
)
return res
end
function postComment (comment, taskid, recip, isQuestion)
local res = SMART:execute_action('PostComment', taskid, 'task', {
CommentAuthor = WA_ROBOT,
CommentText = comment,
Task = taskid,
Recipients = recip,
RecipientGroups = nil,
ForcedEmail = false,
CommentSMS = false,
NoSubscription = false,
TextAsHTML = false,
MarkAsQuestion = isQuestion,
RecipCopies = nil,
CommentType = 3,
CommentVisibleOnlyToRealRecipients = false,
SilentComment = false,
RealUserId = nil
})
return res
end
--postComment (PARAMS, 925710, nil, false)
--[=[
SMART:execute_action('PostComment', 925710, 'task', {
CommentAuthor = 3,
CommentText = EVENTPARAMS["PublishedObjectParameters"],
Task = 925710,
Recipients = nil,
RecipientGroups = nil,
ForcedEmail = false,
CommentSMS = false,
NoSubscription = false,
TextAsHTML = false,
MarkAsQuestion = false,
RecipCopies = nil,
CommentType = 3,
CommentVisibleOnlyToRealRecipients = false,
SilentComment = false,
RealUserId = nil
})
]=]
-- Обработка входящего вебхука с ошибкой
if PARAMS["requestBody"]["messages"] ~= nil and PARAMS["requestBody"]["messages"][1]['status'] == 'error' then
-- Логирование в базу
INSERTED_ID = SQL:scalar(
[[
insert into cm_whatsapp_webhooks_log
select
getdate(), --date
0, --direction - входящее
@webhook, --webhook
JSON_QUERY(@webhook, '$.requestBody.messages'), --messages
JSON_QUERY(@webhook, '$.requestBody.statuses'), --statuses
isnull(JSON_VALUE(@webhook, '$.requestBody.messages[0].messageId'), JSON_VALUE(@webhook, '$.requestBody.statuses[0].messageId')), --messageId
isnull(JSON_VALUE(@webhook, '$.requestBody.messages[0].status'), JSON_VALUE(@webhook, '$.requestBody.statuses[0].status')), --status
cast(JSON_VALUE(@webhook, '$.requestBody.messages[0].isEcho') as bit), --isEcho
null --commentId
--select @@identity
select IDENT_CURRENT('cm_whatsapp_webhooks_log')
]],
{webhook = PARAMS}
)
-- Получаем задачу чата
local chatTask = SQL:scalar(
[[
select t.taskid
from tasksinsubcat117denormalized t (nolock)
join Comments c (nolock)
on c.taskid = t.taskid
join cm_whatsapp_webhooks_log lg (nolock)
on lg.commentid = c.commentid
where lg.messageid = @messageid
and t.extparam888value = @channelId
and t.extparam889value = @chatid
and t.isclosed = 0
]],
{
messageid = PARAMS["requestBody"]["messages"][1]['messageId'],
chatid = PARAMS["requestBody"]["messages"][1]['chatId'],
channelId = PARAMS["requestBody"]["messages"][1]['channelId']
}
)
local errText = 'Возникла непредвиденная ошибка при получении данных из WhatsApp.'
if PARAMS["requestBody"]["messages"][1]['error']['error'] == 'BAD_CONTACT' then
--local errText = PARAMS["requestBody"]["messages"][1]['error']['error'] .. '. ' .. PARAMS["requestBody"]["messages"][1]['error']['description']
errText = 'WhatsApp на данном номере не зарегистрирован.'
end
--Комментарий об ошибке
postComment (errText, chatTask, nil, false)
--Отклоняем задачу чата
SMART:execute_action('SetState', chatTask, 'task', {
Task = chatTask,
Steper = WA_ROBOT,
State = 2,
ThrowErrorOnNoStep = true,
Reason = nil,
BreakTaskLinkForAll = false
})
end
-- Обработка входящего вебхука со статусами
if PARAMS["requestBody"]["statuses"] ~= nil then
-- Логирование в базу
INSERTED_ID = SQL:scalar(
[[
insert into cm_whatsapp_webhooks_log
select
getdate(), --date
0, --direction - входящее
@webhook, --webhook
JSON_QUERY(@webhook, '$.requestBody.messages'), --messages
JSON_QUERY(@webhook, '$.requestBody.statuses'), --statuses
isnull(JSON_VALUE(@webhook, '$.requestBody.messages[0].messageId'), JSON_VALUE(@webhook, '$.requestBody.statuses[0].messageId')), --messageId
isnull(JSON_VALUE(@webhook, '$.requestBody.messages[0].status'), JSON_VALUE(@webhook, '$.requestBody.statuses[0].status')), --status
cast(JSON_VALUE(@webhook, '$.requestBody.messages[0].isEcho') as bit), --isEcho
null --commentId
--select @@identity
select IDENT_CURRENT('cm_whatsapp_webhooks_log')
]],
{webhook = PARAMS}
)
if PARAMS["requestBody"]["statuses"][1]["status"] == 'read' then
local commentid = SQL:scalar(
[[
select top 1 commentId
from cm_whatsapp_webhooks_log
where messageId = @messageId
and commentid is not null
]],
{messageid = PARAMS["requestBody"]["statuses"][1]["messageId"]}
)
if commentid ~= nil then
SQL:query(
[[
update commentrecipients
set isunread = 0, readdate = getdate()
where commentid = @commentid
]],
{commentid = commentid}
)
end
end
end
-- Обработка входящего вебхука
if PARAMS["requestBody"]["messages"] ~= nil and PARAMS["requestBody"]["messages"][1]['status'] == 'inbound' then
-- Логирование в базу
INSERTED_ID = SQL:scalar(
[[
insert into cm_whatsapp_webhooks_log
select
getdate(), --date
0, --direction - входящее
@webhook, --webhook
JSON_QUERY(@webhook, '$.requestBody.messages'), --messages
JSON_QUERY(@webhook, '$.requestBody.statuses'), --statuses
isnull(JSON_VALUE(@webhook, '$.requestBody.messages[0].messageId'), JSON_VALUE(@webhook, '$.requestBody.statuses[0].messageId')), --messageId
isnull(JSON_VALUE(@webhook, '$.requestBody.messages[0].status'), JSON_VALUE(@webhook, '$.requestBody.statuses[0].status')), --status
cast(JSON_VALUE(@webhook, '$.requestBody.messages[0].isEcho') as bit), --isEcho
null --commentId
--select @@identity
select IDENT_CURRENT('cm_whatsapp_webhooks_log')
]],
{webhook = PARAMS}
)
-- INSERTED_ID = 701
-- Получение сообщений из вебхука
MESSAGES = PARAMS["requestBody"]["messages"]
if MESSAGES ~= nil then
CHANNEL_ID = MESSAGES[1].channelId
CONTACT_NAME = MESSAGES[1].contact.name
CHAT_ID = MESSAGES[1].chatId
-- Проверка контактного лица по телефону
CONTACT_TASK_ID = SQL:scalar(
[[
select t49.taskid
from tasksinsubcat47denormalized t47 (nolock)
join extparamtable565denormalized tb (nolock)
on tb.Column58NativeValue = t47.taskid
join tasksinsubcat49denormalized t49 (nolock)
on t49.taskid = tb.taskid
where t47.extparam73value = @phone
and isnull(t49.extparam94value, '') <> ''
--select tb.taskid
--from extparamtable1141denormalized tb (nolock)
--where tb.column110value = @phone
]],
{phone = CHAT_ID}
)
if CONTACT_TASK_ID == nil then
-- Создание новое контактное лицо
local contactExtParams = {}
contactExtParams[1] = '{"ExtParamId": 171, "FixedValue": "' .. CONTACT_NAME .. '"}'
contactExtParams[2] = '{"ExtParamId": 613, "FixedValue": "' .. CHAT_ID .. '"}'
--contactExtParams[3] = '+[{111:{"First":"' + ДП1 + '"}, 222:{"First":"'+ ВСтроку(ДП2) +'"} }]'
local createdContact = createTask (WA_ROBOT, CONTACT_SUBCAT, "", {}, contactExtParams)
CONTACT_TASK_ID = createdContact[0]
end -- Конец создания контактного лица
-- var_dump(CONTACT_TASK_ID)
-- Получение данных контактного лица по телефону
CONTACT_INFO = SQL:query_one(
[[
select
t47.taskid as identTaskid,
t49.taskid as contactTaskid,
-- t49.extparam177value as contactLastname,
-- t49.extparam171value as contactName,
iif(t49.extparam177value is null or t49.extparam177value = '', t49.extparam171value, t49.extparam177value + ' ' + t49.extparam171value) as contactName,
t49.extparam271value as contactResponsible,
t49.extparam271nativevalue as contactResponsibleTaskId,
t30.extparam367nativevalue as contactResponsibleId
from tasksinsubcat47denormalized t47 (nolock)
join extparamtable565denormalized tb (nolock)
on tb.Column58NativeValue = t47.taskid
join tasksinsubcat49denormalized t49 (nolock)
on t49.taskid = tb.taskid
left join tasksinsubcat30denormalized t30 (nolock)
on t30.taskid = t49.extparam271nativevalue
where t47.extparam73value = @phone
]],
{phone = CHAT_ID}
)
-- var_dump(CONTACT_INFO)
-- Получаем задачу чата, если она существует
local chatTaskInfo = SQL:query_one(
[[
select t.taskid, t.stateid
from tasksinsubcat117denormalized t (nolock)
where t.extparam580nativevalue = @contact
and t.extparam888value = @channelId
and t.extparam889value = @chatid
and t.isclosed = 0
]],
{contact = CONTACT_TASK_ID, chatid = CHAT_ID, channelId = CHANNEL_ID}
)
-- CHAT_TASK_ID = chatTaskInfo.taskid
-- CHAT_STATE_ID = chatTaskInfo.stateid
-- var_dump(CHAT_TASK_ID)
-- var_dump(CHAT_STATE_ID)
-- Если задачи чата нет, то создаем ее
if chatTaskInfo == nil then
local chatTaskText = CONTACT_INFO.contactName .. ' (' .. CHAT_ID .. ')'
local chatPerformers = {}
chatPerformers[1] = WA_ROBOT
local chatExtParams = {}
chatExtParams[1] = '{"ExtParamId": 580, "FixedValue": ' .. CONTACT_TASK_ID .. '}'
chatExtParams[2] = '{"ExtParamId": 888, "FixedValue": "' .. CHANNEL_ID .. '"}'
chatExtParams[3] = '{"ExtParamId": 889, "FixedValue": "' .. CHAT_ID .. '"}'
if CONTACT_INFO.contactResponsibleTaskId ~= nil then
chatExtParams[4] = '{"ExtParamId": 271, "FixedValue": ' .. CONTACT_INFO.contactResponsibleTaskId .. '}'
end
local createdTask = createTask (SYS_ROBOT, WA_SUBCAT, chatTaskText, chatPerformers, chatExtParams)
CHAT_TASK_ID = createdTask[0]
CHAT_STATE_ID = 1
else
CHAT_TASK_ID = chatTaskInfo.taskid
CHAT_STATE_ID = chatTaskInfo.stateid
end -- Конец создания задачи чата
-- адресат комментария и вопрос в зависимости от статуса
recipients = {}
isQuestion = true
if CHAT_STATE_ID ~= 99 then
table.insert(recipients, WA_ROBOT)
isQuestion = false
else
if CONTACT_INFO.contactResponsibleId ~= nil then
table.insert(recipients, CONTACT_INFO.contactResponsibleId)
else
local usersInGroup928 = SQL:query('select ug.userid from usergroups ug (nolock) where ug.groupid = 928', {})
for k, v in pairs(usersInGroup928) do
table.insert(recipients, v.userid)
end
end
isQuestion = false
end
-- Обработка сообщений
for num, message in pairs(MESSAGES) do
-- на всякий случай проверим на скобки
-- local msgchunked = message:gsub('^%[', ''):gsub('%]$', '')
-- local msg = UTILS:json_decode(msgchunked)
local isEcho = message.isEcho
local msgType = message.type
local msgText = message.text
local msgAuthorName = message.authorName
local quotedMsgId = nil
if message.quotedMessage ~= nil then
quotedMsgId = message.quotedMessage.messageId
end
local msgContentUri = nil
local msgFileName = nil
if message.contentUri ~= nil then
msgContentUri = message.contentUri
msgFileName = message.contentUri:match('%=(.+%.%w+)$')
-- ext2 = str:match('%.(%w+)$')
end
-- если сообщение типа текст
if msgType == 'text' and msgText ~= '' and msgText ~= nil then
if CHAT_STATE_ID == 1 then
postedComment = postComment (msgText, CHAT_TASK_ID, recipients, isQuestion)
SQL:query('update cm_whatsapp_webhooks_log set commentId = @postedComment where id = @logId', {postedComment = postedComment, logId = INSERTED_ID})
SMART:execute_action('MakeStep', CHAT_TASK_ID, 'task', {
Task = CHAT_TASK_ID,
Steper = SYS_ROBOT,
Step = 926,
Reason = nil,
DoNotWriteComment = false
})
elseif CHAT_STATE_ID == 98 and msgText ~= '1' and msgText ~= '2' and msgText ~= '3' and msgText ~= '4' then
SMART:execute_action('MakeStep', CHAT_TASK_ID, 'task', {
Task = CHAT_TASK_ID,
Steper = WA_ROBOT,
Step = 927,
Reason = nil,
DoNotWriteComment = false
})
--CHAT_STATE_ID = 99
recipients = {}
if CONTACT_INFO.contactResponsibleId ~= nil then
table.insert(recipients, CONTACT_INFO.contactResponsibleId)
else
local usersInGroup928 = SQL:query('select ug.userid from usergroups ug (nolock) where ug.groupid = 928', {})
for k, v in pairs(usersInGroup928) do
table.insert(recipients, v.userid)
end
end
isQuestion = true
postedComment = postComment (msgText, CHAT_TASK_ID, recipients, isQuestion)
SQL:query('update cm_whatsapp_webhooks_log set commentId = @postedComment where id = @logId', {postedComment = postedComment, logId = INSERTED_ID})
SMART:execute_action('PostComment', CHAT_TASK_ID, 'task', {
CommentAuthor = 2110,
CommentText = 'Ваш вопрос направлен менеджеру',
Task = CHAT_TASK_ID,
Recipients = {2349},
RecipientGroups = nil,
ForcedEmail = false,
CommentSMS = false,
NoSubscription = false,
TextAsHTML = false,
MarkAsQuestion = false,
RecipCopies = nil,
CommentType = 3,
CommentVisibleOnlyToRealRecipients = false,
SilentComment = false,
RealUserId = nil
})
else
postedComment = postComment (msgText, CHAT_TASK_ID, recipients, isQuestion)
SQL:query('update cm_whatsapp_webhooks_log set commentId = @postedComment where id = @logId', {postedComment = postedComment, logId = INSERTED_ID})
end
--msgText = '[' .. contactInfo.contactName .. '] ' .. msgText
--postedComment = postComment (msgText, CHAT_TASK_ID, recipients, isQuestion)
--SQL:query('update cm_whatsapp_webhooks_log set commentId = @postedComment where id = @logId', {postedComment = postedComment, logId = INSERTED_ID})
end
-- если сообщение типа image, audio, video, document
if msgType == 'image' or msgType == 'audio' or msgType == 'video' or msgType == 'document' then
--скачивание файла
local download = SMART:execute_action('DownloadFile', CHAT_TASK_ID, 'task', {
Task = CHAT_TASK_ID,
UploadingUserName = WA_ROBOT,
TargetExternalParameter = nil,
FileLink = msgContentUri,
Comment = nil,
DownloadManyFiles = false,
FileMask = nil,
FileCreateDateFrom = nil,
FileCreateDateTo = nil,
FileName = msgFileName,
HttpMethod = nil,
HttpParameters = {},
HttpHeaders = {},
HttpRequestBody = nil
})
local fileId = UTILS:json_decode(download)["Id"]
postedComment = SQL:scalar('select CommentId from FileStorageFileToCommentLinks (nolock) where FileId = @fileid and Taskid = @taskid', {fileid = fileId, taskid = CHAT_TASK_ID})
if msgText ~= nil and msgText ~= '' then
--msgText = '[' .. contactInfo.contactName .. '] ' .. msgText
-- postedComment = postComment (msgText, CHAT_TASK_ID, recipients, isQuestion)
-- SQL:query('update cm_whatsapp_webhooks_log set commentId = @postedComment where id = @logId', {postedComment = postedComment, logId = INSERTED_ID})
SQL:query('update comments set content = @content, typeid = 3, eventid = null where commentid = @commentid', {commentid = postedComment, content = msgText})
-- SQL:query('update cm_whatsapp_webhooks_log set commentId = @commentid where id = @logId', {commentid = postedComment, logId = INSERTED_ID})
end
SQL:query('update cm_whatsapp_webhooks_log set commentId = @postedComment where id = @logId', {postedComment = postedComment, logId = INSERTED_ID})
end --msgType == 'image'
-- Если есть цитированное сообщение
if quotedMsgId ~= nil then
--ищем ID комментария в логе
local replyComment = SQL:scalar('select commentId from cm_whatsapp_webhooks_log (nolock) where messageId = @quotedMessageId', {quotedMessageId = quotedMsgId})
--если ID есть, то обновляем комментарии
if replyComment ~= nil then
SQL:query('update Comments set InReplyToCommentID = @replyComment where CommentID = @postedComment', {postedComment = postedComment, replyComment = replyComment})
end
end -- Конец цитирования
end -- Конец цикла по сообщениям
end -- Конец if MESSAGES
end -- Конец обработки входящего вебхука
|
