Использование SQL для создания приложения голосовой рассылки с Twilio

*Austin Henderson — разработчик и энтузиаст, увлеченный проектированием эффективных, избыточных и целостных решений в каждой возможной ситуации. Ниже приведено подробное описание того, как создать приложение голосовой рассылки с использованием SQL и Twilio.*

*Austin — разработчик проектов twiSQL и twiCLI, эта статья была первоначально опубликована на его блоге.*

Чтобы упростить использование сервисов Twilio, я создал два специальных проекта, которые можно скачать и использовать бесплатно. twiCLI — это утилита командной строки для среды Windows, предназначенная для совершения телефонных звонков и отправки текстовых сообщений, а twiSQL — это набор пользовательских функций SQL для совершения телефонных звонков и отправки текстовых сообщений из кода TSQL.

В этой статье я говорю о проекте twiSQL, и цель состоит в том, чтобы показать, как создать полную систему звонков с использованием только SQL, Twilio и библиотечных функций twiSQL. Для обеспечения переносимости файл документации был обновлен с добавлением этого образца проекта в конец.

Иногда лучший способ понять идею — это увидеть ее в действии в виде рабочего проекта. В этом образце проекта мы проиллюстрируем идею списка потенциальных клиентов, которым необходимо позвонить в рамках телефонной кампании.

Сначала мы создаем таблицы для хранения деталей этих транзакций.

[sourcecode language=»sql»]

CREATE TABLE [dbo].[myProspects](

[ProspectID] [int] IDENTITY(1,1) NOT NULL,

[ProspectName] [varchar](max) NULL,

[ProspectPhone] [varchar](25) NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[myProspectCalls](

[CallID] [int] IDENTITY(1,1) NOT NULL,

[ProspectID] [int] NULL,

[CallDate] [datetime] NULL,

[CallSID] [varchar](255) NULL,

[CallStatus] [int] NULL,

[CallDetails] [varchar](max) NULL

) ON [PRIMARY]

[/sourcecode]

В примере кода ниже мы вставляем несколько записей в таблицу myProspects, а затем проходим через эти записи, совершая телефонные звонки и воспроизводя предварительно определенное приветствие.

[sourcecode language=»sql»]

— ОЧИСТКА ТАБЛИЦЫ ДЛЯ ПРОСПЕКТОВ – ЭТО ТОЛЬКО ДЛЯ ТЕСТОВОГО ПРОХОДА

truncate table myProspects

— СОЗДАНИЕ ЗАПИСЕЙ ПРОСПЕКТОВ – НОРМАЛЬНО ЭТО БЫЛО БЫ ЗАГРУЖЕНО ИЗ ФАЙЛА

insert into myProspects (ProspectName, ProspectPhone)

select ‘John Doe’,’18005551212′

insert into myProspects (ProspectName, ProspectPhone)

select ‘Bill Smith’, ‘18005551212′

— ЭТОТ РАЗДЕЛ ОБРАБАТЫВАЕТ СОВЕРШЕНИЕ ЗВОНКОВ

declare @prospectID int

declare @callingPhoneNumber varchar(11)

declare @prospectPhone varchar(25)

declare @prospectName varchar(255)

declare @CallSID varchar(255)

declare @authID varchar(255)

declare @token varchar(255)

declare @whatToSay varchar(max)

declare @whatToSayTemplate varchar(max)

— ЗДЕСЬ ВЫ БЫЛИ БЫ ИСПОЛЬЗОВАТЬ СВОЕ ПЕРСОНАЛИЗИРОВАННОЕ СООБЩЕНИЕ ДЛЯ ГОВОРЕНИЯ

select @whatToSayTemplate = ‘Hello %NAME%. As a representative of We Make Stuff company I would like to thank you for reaching out to our agent for your needs. If you should need to reach us at any time please feel free to call 888-555-1212 for prompt assistance. We sincerly appreciate your time, and please remember to look us up on Facebook. Have a fantastic day!’

— ВЫ НУЖНО ЗАПОЛНИТЬ СВОИ AUTHID И TOKEN ЗНАЧЕНИЯ ОТ TWILIO

select @authID = ‘XXXXXXXXXXXXXXXXXXXXXXXX’

select @token = ‘XXXXXXXXXXXXXXXXXXXXXXXXX’

— ВЫ ДОЛЖНЫ ИМЕТЬ НОМЕР ТЕЛЕФОНА С TWILIO – ЗАМЕНИТЕ НОМЕР НИЖЕ

select @callingPhoneNumber = ‘16158008999′

— ПРОВЕРКА, ЧТОБЫ ПОЛУЧИТЬ ПЕРВЫЙ ПРОСПЕКТ

select @prospectID =

(select top 1 ProspectID from myProspects order by ProspectID asc)

— У НАС ЕСТЬ ЗВОНКИ, КОТОРЫЕ НАДО СДЕЛАТЬ ЗДЕСЬ, ДАВАЙТЕ ПРИСТУПИМ К РАБОТЕ

while isnull(@prospectID,0) > 0

begin

— ПОЛУЧЕНИЕ ПЕРЕМЕННЫХ ДЛЯ ИСПОЛЬЗОВАНИЯ В НАШЕМ ЗВОНКЕ

select @prospectPhone = ProspectPhone, @prospectName = prospectName

from myProspects where ProspectID = @prospectID

— НОМЕР ДОЛЖЕН БЫТЬ 11 ЦИФР

if isnumeric(@prospectPhone) = 1 and len(@prospectPhone) = 11

begin

select @prospectPhone

— ЗАМЕНА ИМЕНИ В НАШЕМ СТРОКЕ ДЛЯ ГОВОРЕНИЯ – ПРИЯТНЫЙ ШТРИХ

select @whatToSay = replace(@whatToSayTemplate,’%NAME%’,@prospectName)

— ЗДЕСЬ МЫ СОВЕРШАЕМ ЗВОНКОВ ЧЕРЕЗ НАШ УДФ

SELECT @CallSID = dbo.msgTwilioMakeCall(@prospectPhone,@callingPhoneNumber,@whatToSay,’m’,@authID,@token)

if charindex(‘|’,@CallSID) > 0

begin

SELECT @CallSID =

right(@CallSID,len(@CallSID) – charindex(‘|’,@CallSID))

insert myProspectCalls (ProspectID,CallDate,CallSID,CallStatus)

values (@prospectID,getdate(),@CallSID,0)

end

else

begin

insert myProspectCalls (ProspectID,CallDate,CallSID,CallStatus)

values (@prospectID,getdate(),@CallSID,-1)

end

— ПОЛУЧЕНИЕ СЛЕДУЮЩЕГО ПРОСПЕКТА

select @prospectID = (select top 1 prospectID from myProspects

where prospectID > @prospectID order by prospectID asc)

end

end

[/sourcecode]

Теперь, когда мы совершили телефонные звонки, в какой-то момент в будущем вы можете захотеть узнать, был ли звонок действительно завершен. Код ниже используется для иллюстрации того, как можно использовать предоставленные функции для проверки деталей конкретного звонка.

[sourcecode language=»sql»]

— ЭТА ЛОГИКА БУДЕТ ПРОВЕРЯТЬ СТАТУС ЗВОНКОВ

declare @authID varchar(255)

declare @token varchar(255)

— ВЫ НУЖНО ЗАМЕНИТЬ СВОИ AUTHID И TOKEN

select @authID = ‘XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX’

select @token = ‘XXXXXXXXXXXXXXXXXXXXXXXXXXX’

declare @callSID varchar(255)

declare @callID int

declare @callDetails varchar(max)

declare @callStatus int

— МЫ БУДЕМ НАСТРАИВАТЬ ЦИКЛ ЗДЕСЬ, ЧТОБЫ ПРОЙТИ ЧЕРЕЗ ЗАПИСИ

select @callID =

(select top 1 CallID from myProspectCalls where CallStatus = 0 order by callID asc)

while isnull(@callID,0) > 0

begin

select @callSID = callSID from myProspectCalls where callID = @callID

select @callDetails = dbo.msgTwilioViewCall(@callSID,@authID,@token)

— ПОЛНОЕ XML ВОЗВРАЩАЕТСЯ В ПЕРЕМЕННОЙ @callDetails

— ВЫ МОЖЕТЕ ОБРАБОТАТЬ ЭТО XML КАК УГОДНО ВАМ – В НЕМ СОДЕРЖИТСЯ ХОРОШАЯ

— ИНФОРМАЦИЯ – ДЛЯ ЭТОГО СЛУЧАЯ МЫ ПРОСТО ПРОВЕРЯЕМ СТАТУС

if charindex(‘|completed|’,@callDetails) > 0

begin

select @callStatus = 1

end

else

begin

select @callStatus = 0

end

— ОБНОВЛЕНИЕ СТАТУСА ЗАПИСИ ЗВОНКА

update myProspectCalls

set callDetails = @callDetails, callStatus = @callStatus

where callID = @callID

— ПОЛУЧЕНИЕ СЛЕДУЮЩЕГО ЗАПИСИ ЗВОНКА

select @callID =

(select top 1 CallID from myProspectCalls

where CallStatus = 0 and callID > @callID

order by callID asc)

end

[/sourcecode]

Еще один вариант, который стоит рассмотреть, заключается в том, что вместо использования движка текста в речь можно записать файл mp3, разместить его на публичном URL и передать URL файла в качестве текста звонка. Этот вариант обеспечивает очень персонализированную кампанию с небольшим изменением процесса.

Когда мы совершаем звонок msgTwilioMakeCall(), мы просто заменяем переменную @whatToSay на URL файла. Простой способ сделать это — использовать функцию общей ссылки Dropbox — или любой другой публичный URL. В примере ниже мы используем Dropbox.

[sourcecode language=»sql»]

SELECT @CallSID = dbo.msgTwilioMakeCall(@prospectPhone,@callingPhoneNumber, ‘http://dl.dropbox.com/u/1541272/recorded.mp3′,’m’,@authID,@token)

[/sourcecode]

Используя вышеуказанный метод, вы можете легко создать знакомый компонент телефонной кампании, например, напоминание о приеме у дантиста, и все это почти без каких-либо расходов.

Надеюсь, этот образец проекта помогает вам понять, что возможно в очень простых шагах и с очень ограниченным воздействием на кошелек.