Как сделать обработку входящей почты (создание клиента в 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.