Как сделать обработку входящей почты (создание клиента в CRM из Email)

Представим, что у нас есть некая CRM и почтовый ящик. Наша задача - на входящие письма создавать лида в CRM либо добавлять письмо к существующему клиенту. 

Данный подход реализован в решении F-CRM + Site (сайт компании на лендингах со встроенной CRM). https://crm.web-automation.ru/

Как это реализовать: 

1. Создаем структуру таблиц для хранения писем: 

CREATE TABLE [dbo].[crm_emails](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[guid] [uniqueidentifier] NULL,
	[from] [nvarchar](128) NULL,
	[to] [nvarchar](128) NULL,
	[cc] [nvarchar](128) NULL,
	[bcc] [nvarchar](128) NULL,
	[date] [nvarchar](128) NULL,
	[headers] [nvarchar](max) NULL,
	[htmlBody] [nvarchar](max) NULL,
	[inReplyTo] [nvarchar](512) NULL,
	[subject] [nvarchar](512) NULL,
	[textBody] [nvarchar](max) NULL,
	[uniqueId] [nvarchar](512) NULL,
	[messageId] [nvarchar](128) NULL,
	[fromDisplayName] [nvarchar](128) NULL,
 CONSTRAINT [PK_crm_emails] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[crm_emailFiles](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[created] [datetime] NULL,
	[letterID] [nvarchar](128) NULL,
	[filename] [nvarchar](512) NULL,
	[size] [decimal](18, 2) NULL,
	[url] [nvarchar](512) NULL,
 CONSTRAINT [PK_crm_emailFiles] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

2. Добавляем в минутные периодические действия вызов процедуры, которая будет забирать почту каждую минуту (в /syssp): 

CREATE PROCEDURE [dbo].[falcon_nt_sync_min]
AS
BEGIN
    declare @ps ExtendedDictionaryParameter 
    exec [dbo].[crm_takeEmails] @parameters = @ps 
END

3. Реализуем процедуру crm_takeEmails, которая вызывает внешнее действие сбора почты: 

CREATE     PROCEDURE [dbo].[crm_takeEmails]
	@parameters ExtendedDictionaryParameter readonly
AS
BEGIN 
	declare @folder nvarchar(max) = 'INBOX'
    declare @server nvarchar(128) = dbo.as_setting('takeEmail.server', 'imap.mail.ru'),  
		@port int = try_cast(dbo.as_setting('takeEmail.port', '993') as int), 
		@login nvarchar(128)= dbo.as_setting('takeEmail.login', ''), 
		@password nvarchar(128) = dbo.as_setting('takeEmail.password', ''),	
		@maxDate datetime = (select max(date) from crm_emails)
    
       
    if(@maxDate is null) set @maxDate = dateadd(day, -30, getdate())
   declare @options nvarchar(max) = '{ "Folder": "'+@folder+'", "PageSize": 100, "Page": 1, "DeliveredAfter": "'+cast(@maxDate as nvarchar)+'" }'
    print @options
	-- SELECT 2 Вызов внешнего действия
	select 'mail' type, 'receiveEmails' action, @server server, 
    	@port port, @login login, @password password, 
        @options options

END

В настройках нам надо прописать сервер IMAP, порт, логин, пароль для ящика, на который будет отправляться почта извне. 

maxDate определяем от какой даты брать письма с почтового ящика. 

4. Когда письма будут получены, для каждого из них во внешнем действии mail receiveEmails вызываются процедуры [as_mail_saveLetter] (сохранение самого письма), [as_mail_saveLetterFile] (сохранение вложений). 

Вот эти процедуры. 

as_mail_saveLetter (извлекаем все параметры, проверяем нет ли у нас этого письма, и если нет - то сохраняем в базе). 

CREATE   PROCEDURE [dbo].[as_mail_saveLetter]
@parameters ExtendedDictionaryParameter READONLY,
@username nvarchar(256)
as
begin
	declare @MessageId nvarchar(max)
	select @MessageId = Value2 from @parameters where [Key] = 'MessageId'

	declare @Bcc nvarchar(max)
	select @Bcc = Value2 from @parameters where [Key] = 'Bcc'

	declare @Cc nvarchar(max)
	select @Cc = Value2 from @parameters where [Key] = 'Cc'

	declare @Date datetime
	select @Date = try_cast(Value2 as datetime) from @parameters where [Key] = 'Date'

	declare @From nvarchar(max)
	select @From = Value2 from @parameters where [Key] = 'From'

	declare @Headers nvarchar(max)
	select @Headers = Value2 from @parameters where [Key] = 'Headers'

	declare @Attachments nvarchar(max)
	select @Attachments = Value2 from @parameters where [Key] = 'Attachments'


	declare @HtmlBody nvarchar(max)
	select @HtmlBody = Value2 from @parameters where [Key] = 'HtmlBody'

	declare @InReplyTo nvarchar(max)
	select @InReplyTo = Value2 from @parameters where [Key] = 'InReplyTo'

	declare @Subject nvarchar(max)
	select @Subject = Value2 from @parameters where [Key] = 'Subject'


	declare @TextBody nvarchar(max)
	select @TextBody = Value2 from @parameters where [Key] = 'TextBody'

	declare @To nvarchar(max)
	select @To = Value2 from @parameters where [Key] = 'To'

	declare @UniqueId nvarchar(max)
	select @UniqueId = Value2 from @parameters where [Key] = 'UniqueId'

	declare @IsNew bit
	select @IsNew = try_cast(Value2 as bit) from @parameters where [Key] = 'IsNew'

	declare @folder nvarchar(max) = (select Value2 from @parameters where [Key] = 'Folder')
	declare @login nvarchar(max) = (select Value2 from @parameters where [Key] = 'Login')
	
   
	declare @guid uniqueidentifier
	select @guid = try_convert(uniqueidentifier, Value2) from @parameters where [Key] = 'Guid'

	declare @returnId int = (select id from crm_emails where messageId = @messageId) 
	if(@returnId is null) begin 
		insert into [crm_emails] (guid, [from], fromDisplayName, [to], cc, bcc, date, headers, htmlBody, inReplyTo, 
			subject, textBody, uniqueId,  MessageId)
		values(@guid, dbo.str_splitPart(@from, '||',1),  dbo.str_splitPart(@from, '||',2),
			@to, @cc, @bcc, @date, @headers, @htmlBody, @inReplyTo,
			@subject, @textBody, @uniqueId,  @MessageId)
		set @returnId = scope_identity()

		exec as_mail_processLetter @letterID = @returnId
	end 
	-- SELECT 1
	select @returnId

end
	

as_mail_saveLetterFile: 

CREATE   PROCEDURE [dbo].[as_mail_saveLetterFile]
@parameters ExtendedDictionaryParameter READONLY,
@username nvarchar(256),
@guid uniqueidentifier
as
begin
	declare @filename nvarchar(max) = (select Value2 from @parameters where [Key] = 'Filename')
	declare @url nvarchar(max) = (select Value2 from @parameters where [Key] = 'Url')

	declare @size bigint = (select try_cast(Value2 as bigint) from @parameters where [Key] = 'Size')

	declare @letterID int = (select top 1 id from crm_emails where guid = @guid)
	declare @returnID int = (select id from crm_emailFiles  where letterID = @letterID and lower(filename) = lower(@filename) )
	
	if(@returnID is null) begin
		insert into crm_emailFiles(created, letterID, filename, size, url)
		values (getdate(), @letterID, @filename, @size, @url)

		set @returnID = scope_identity()
    end 
	
	-- SELECT 1
	select @returnID
end

Последняя процедура - это обработка каждого входящего письма - после его сохранения в as_mail_saveLetter мы вызываем процедуру as_mail_processLetter, где обрабатываем входящее письмо в плане связи с CRM:

CREATE   PROCEDURE [dbo].as_mail_processLetter
	@letterID int
as
begin
	declare @from nvarchar(max),  @name nvarchar(max),  @subject nvarchar(max) 
	
	select @from = lower([from]), @name = iif(len(fromDisplayName)>0, fromDisplayName, [from]), @subject = subject
	from crm_emails where id = @letterID
	
	
	declare @contactID int = (select top 1 id from ctr_contacts where lower(email) = @from)
	declare @clientID int, @instanceID int
	if(@contactID is null) begin 
		-- создаем лида 
		declare @ps ExtendedDictionaryParameter
        insert into @ps ([Key], Value2)
        select 'phone' [Key], '' Value2  union
        select 'email' [Key], @from Value2  union      
        select 'referrer' [Key], 'почта' Value2        
        
		exec  [dbo].[crm_createClient]
        	@name = @name,
        	@comment = 'Создан на основе входящего письма',
			@parameters = @ps,
			@username = '',
			@clientID = @clientID output    
		select @instanceID = instanceID from crm_clients where id = @clientID      
	end else begin 
		select top 1 @instanceID  = instanceID, @clientID = id 
		from crm_clients 
		where contragentID in (select contragentID from ctr_contacts where id = @contactID)
	end

	exec [dbo].[crm_clientComment] @username= '', @text= '<b>Пришло новое письмо от клиента</b>', @instanceID= @instanceID, @color = '#000'


	declare @newContactNotificationTo  nvarchar(max) = dbo.as_setting('newContactNotificationTo', '');
    declare @domain nvarchar(max) = dbo.as_setting('domain', 'site.ru')
    if(len(@newContactNotificationTo)>0) begin 
    	
		declare @text nvarchar(max) = 'Новое обращение с почты - '+ isnull(@name, '') + ' ' + isnull(@from, '') + '  ' + isnull(@subject, '')
		declare @seller nvarchar(128) = (select top 1 seller from crm_clients where id = @clientID)
		declare @to nvarchar(128) = iif(len(@seller)>0, @seller, 
			(select top 1 ltrim(rtrim(value))  from dbo.split(@newContactNotificationTo, ',') where len(value)>0) )
		declare @url nvarchar(max) = 'https://'+@domain+'/client/'+ cast(@instanceID as nvarchar)

		exec dbo.as_nt_createNotification 
			@additional = '',
			@from = '',
			@to = @to,
			@url = @url,
			@text = @text,
			@typeCode='common',
			@returnID = 0
	end
end
	

Мы пробуем здесь найти подходящий контакт клиента. Если его нет, то создаем клиента в CRM. Далее добавляем к карточке комментарий и отправляем уведомление менеджеру о новом обращении с почты. 

Таким образом, мы получили письмо извне на нашу почту и создали лида в CRM. Далее можно организовать отправку сообщения с сайта (форма письма + отправка через внешнение действие email). 

Подобный подход можно посмотреть на стенде решения F-CRM + Site.

Falcon Space - функциональная веб-платформа разработки на узком стеке MS SQL/Bootstrap. Вводная по Falcon Space
Насколько полезной была статья?

Google поиск по нашей документации

Falcon Space

Это снижение стоимости владения

за счет меньшего количества людей для поддержки и узкого стека разработки. Про снижение стоимости владения продуктом

Это быстрое внесение изменений

по ходу эксплуатации программы. Как создается функционал на платформе

Это простой удобный интерфейс

адаптация под мобильные устройства. Про юзабилити платформы

Нужна бесплатная консультация?
Получить оценку проекта
Создайте концепцию проекта на основе нашего шаблона и получите оценку проекта в виде КП.
Демо-сайт решений
Базисные решения, которые можно гибко адаптировать под себя: менять внешний вид, бизнес-логику и даже структуру базы данных.
Сайт использует Cookie. Правила конфиденциальности OK