Урок 20 Работа с SQL-запросом — различия между версиями

Текущая версия на 19:54, 14 ноября 2014

В этом уроке разбирается компонент работы с базами данных "SQL-запрос".

Введение

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

Базы данных применяются для хранения сведений по абонентам, информации по коммутациям и различных системных настроек. В своей работе Oktell использует Microsoft SQL Server. Когда вы устанавливаете программу, автоматически создаются 3 системные базы данных:

• oktell - основная БД для хранения оперативной статистики и статистики АТС.
• oktell_settings - БД для хранения настроек комплекса, используемых для загрузки сервера.
• oktell_cc_temp - БД для хранения статистики работы call-центра в пространственных таблицах.

Обращение сервера Oktell к БД всегда осуществляется через основную базу oktell.

Для создания и использования собственной таблицы существуют два способа:

1) Использовать модуль "Таблицы абонентов" в разделе "Call-центр". Созданная таблица будет располагаться в БД Oktell

2) Вручную создать таблицу в любой базе данных с помощью SQL Server Management Studio

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

Компонент позволяет работать:

• с локальными таблицами, которые располагаются в базе данных Oktell
• с таблицами в других базах данных Microsoft SQL Server
• с таблицами другого Microsoft SQL-сервера (ADO-подключение)
• с таблицами MySQL, Oracle, Firebird и т.д. (ODBC подключение, Подключение к Oracle)
• с базами данных Microsoft Access и документами Microsoft Excel (OLE подключение)
• с другими базами данных, которые можно связать с SQL Server

Рассмотрим описанный процесс поподробнее.

Создание таблицы

С помощью модуля "Таблицы абонентов"

ВНИМАНИЕ: Для того, чтобы вы могли использовать этот модуль, в вашей системе должна быть приобретена хотя бы одна лицензия оператора Call-центра.

Чтобы создать таблицу в базе данных Oktell:

• Перейдите в модуль "Проекты". Нажмите Добавить. Создайте проект.
• Перейдите в модуль "Таблицы абонентов". Нажмите Добавить.

• На вкладке "Подключение" введите название таблицы. В примере название таблицы "Lesson20". Нажмите "Далее".
• На вкладке "Данные" добавьте столбцы для хранения данных. В примере добавлены следующие столбцы:

• CallerId - поле для хранения телефона абонента. Тип Nvarchar(auto)
• CalledId - поле для хранения набранного номера. Тип Nvarchar(auto)
• Datetime - поле для хранения времени звонка. Тип Datetime

• Нажмите "Сохранить".

Созданная таблица будет доступна в базе данных Oktell и к ней можно будет обращаться в сценариях. С помощью модуля "Таблицы абонентов" вы можете в любой момент времени посмотреть находящиеся записи в таблице, изменить их значение, удалить строки или очистить таблицу.

Создание таблицы вручную

Если у вас нет модуля Call-центра, вы можете создать таблицы абонентов вручную, пользуясь программой SQL Server Management Studio (SSMS). Для каждой версии Microsoft SQL Server устанавливается соответствующая версия SSMS.

Программа доступна доступна на сайте http://www.microsoft.com/.

Чтобы создать таблицу вручную:

• Зайдите в SSMS, нажмите на кнопку "Создать запрос". Введите следующий запрос: (пример для таблицы Lesson20 (см.выше))

USE [oktell]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Lesson20](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[CallerId] [nvarchar](2000) NULL,
	[CalledId] [nvarchar](2000) NULL,
	[Datetime] [datetime] NULL,
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

Путем дублирования и изменения строк вы можете создавать различные таблицы.

• Нажмите F5 или кнопку "Выполнить". Таблица Lesson20 будет создана в БД Oktell и к ней можно будет обращаться в сценариях.

Использование SQL-запросов в сценариях

Задача: При входящем звонке записывать информацию об абоненте (CallerId, CalledId и время звонка). Вывести уведомление оператору с которым соединится данный абонент.

Сценарий выглядит следующим образом:

Разберем компоненты сценария поподробнее.

• Компонент "Данные абонента". Определяются основные параметры звонка - время, номер абонента и набранный номер.

• Переменной Datetime (дата/время) присваивается значение функции "Текущие дата и время"
• Переменной CalledId (строковая) присваивается значение функции "Внешний номер (CalledId)"
• Переменной CallerId (строковая) присваивается значение функции "Номер абонента (CallerId)"
• Переменной Line (строковая) присваивается значение функции "Guid-идентификатор линии"

• Компонент "INSERT". Выполняет SQL-запрос добавления данных в таблицу Lesson20.

Компонент SQL-запрос настраивается в 3 этапа. 1 этап: Настройка подключения к базе данных. Используется вкладка "Подключение".

По умолчанию, компонент работает с собственной БД Oktell.

• Если указать название таблицы, SQL-запрос будет искать ее в БД Oktell. Например, название table обозначает таблицу [oktell].[dbo].[missed].
• Для обращения к таблицы в другой базе SQL-сервера OKTELL используйте название, например [oktell_cc_temp].[dbo].[A_Cube_CC_Cat_OperatorInfo]

Для обращения к таблицам других серверов на вкладке необходимо выбрать тип подключения и ввести строку подключения к БД.

• ADO подключение - подключение к другому Microsoft SQL Server
• OLE подключение - например, подключение к Microsoft Access или Microsoft Excel.
• Подключение к Oracle
• ODBC подключение - например, подключение к серверам MySQL или Firebird

Для каждого типа показываются примеры строки подключения. Дополнительную информацию вы можете найти на сайте http://www.connectionstrings.com

Использовать другие базы данных можно с помощью линковки их к SQL-серверу OKTELL. Более подробно написано в статье Подключение внешних БД

2 этап: Ввод текста запроса. Используется вкладка "Запрос".

Для того чтобы указать параметры используются служебные символы. Например, чтобы задать параметр x используйте следующие выражение:

• @x (для SQL серверов)
• ? (для ADO и ODBC подключений)
• : (для Oracle)

ВНИМАНИЕ: С помощью служебных символов ? и : можно задать только входные параметры. Выходные параметры записываются сразу в переменные сценария с идентичным названием. Следующий запрос возвращает значения столбцов callerid и calledid в переменные сценария name и number.

select callerid as [name], calledid as number
from maintable 
where dtime=?

Обратите внимание на подсказки в нижней части вкладки.

3 этап: Назначение переменных параметрам запроса. Используется вкладка "Параметры".

Назначьте каждому параметру запроса соответствующую переменную в сценарии. Параметры делятся на два типа:

• Входные - параметры, от которых зависит запрос.
• Выходные - параметры, в которые записывается результат запроса

В нашем сценарии используется запрос таблицу Lesson20 в БД Oktell, поэтому на вкладке Подключение все остается по умолчанию.

На вкладке "Запрос" пишется вводится следующий SQL-запрос

insert into lesson20 (callerid,calledid,[datetime])
values (@callerid,@calledid,@datetime)
select @id=@@IDENTITY

Запрос вносит в таблицу Lesson20 в поля callerid, calledid, datetime параметры @callerid, @calledid и @datetime. После этого с помощью служебного слова @@IDENTITY определяется номер введенной строки.

В запросе входными параметрами являются переменные @callerid,@calledid и @datetime, так как они записываются в поля таблицы и следовательно от них зависит результат SQL-запроса. Выходным параметром является @id, так как его значение вычисляется в ходе выполнения запроса.

ВНИМАНИЕ: Если SQL-таблица имеет поле типа дата/время (datetime), то в это поле рекомендуется записывать переменную также типа дата/время. Неявное преобразование типов из строки во время не всегда получается корректным.

На вкладке Параметры параметрам запроса сопоставляются переменные сценария:

• @calledid - переменная calledid
• @callerid - переменная callerid
• @datetime - переменная Datetime
• @id - переменная id.

• Компонент "На группу". Переключает абонента на групповой номер.

• Назначение: Внутрь
• Номер: 105
• Тип вызова: Обычный

• Компонент "500мс". После того как абонент соединится с каким-либо оператором из группового номера, произойдет запись информации о коммутации в таблицы БД Oktell. Необходимо останавить выполнение сценария на 500мс, для того, чтобы запись о коммутации попала в нужные таблицы и считать ее.

• Время задержки: 500

• Компонент "Скоммутированная линия". Определяет номер скоммутированной линии. Используется компонент "Статус объекта".

Компонент "Статус объекта" - это многофункциональный компонент сценанриев, с помощью которого возможно произвести различные действия над основными объектами системы (пользователь, линия, задача, номер и др.). Имеет множество функций, полный перечень можно найти в статье: Общие компоненты сценариев: Статус объекта

В нашем сценарии используются следующие настройки:

• Действие: Определить
• Тип объекта: Линия
• Ключ линии: переменная "Line"
• Функции: Номер скоммутированной линии/конференции
• Значение в переменную: переменная Chained (строковая)

• Компонент "Идентификатор пользователя". По номеру линии определяет привязанного к ней пользователя в текущий момент. В настройках компонента "Статус объекта" следующие поля:

• Действие: Определить
• Тип объекта: Линия
• Ключ линии: переменная "Chained"
• Функция: Идентификатор пользователя
• Значение в переменную: переменная "IdUser" (строковая)

• Компонент "Отладочное уведомление". Выводит значения основных переменных администратору.

[Line]+endline+[Chained]+endline+[IdUser]+endline+[id]

• Компонент "Уведомление пользователю". Выводит уведомление пользователю Oktell, который скоммутировался с данным абонентом.

• Ключ получателя: переменная IdUser
• Текст:

'Номер звонка '+[id]+endline+
'CallerId '+[CallerId]+endline+
'CalledId '+[CalledId]+endline+
'Время '+[Datetime]

Результаты работы

В таблице Lesson20 формируется следующая информация:

При желании в таблицу можно заносить абсолютно разные данные - имя соединившегося оператора, время ожидания в очереди, время в разговоре и так далее. Подобная задача решается в статье Задача по обработке пропущенных. Статья подходит даже, если у вас нет Call-центра, так как данные вычисляются в главном сценарии.

Скачать сценарий: Урок20.zip

Поздравляем! Вы научились использовать еще несколько компонентов сценариев. Можете переходить к следующему уроку.

Техническая документация: Общие компоненты сценариев и Компоненты сценариев IVR

Вопросы и задания

• Модифицируйте сценарий этого урока. Необходимо также записывать в таблицу имя оператора и время начала соединения абонента с оператором.
• Реализуйте сценарий сбора статистики по пропущенным звонкам, пользуясь статьей: Задача по обработке пропущенных
• Создайте IVR-сценарий, при звонке на который вам на почту будет отправляться информация из таблицы по всем соединениям за день. Воспользуйтесь статьей Построковая обработка sql выборки в сценарии
• Модифицируйте сценарий исходящей маршрутизаций. Необходимо записывать всю информацию по исходящим звонкам (время звонка, набираемый номер, имя пользователя, внутренний или внешний номер).