Просмотр исходного текста страницы Урок 20 Работа с SQL-запросом

{|cellpadding="10" cellspacing="0" border="0"
| [[Уроки | Наверх]]
| [[Урок 19 Обработка неудачного соединения | К предыдущему уроку]]
| [[Урок 21 Web-запросы | К следующему уроку]]
|-
|}

__TOC__

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


[[Файл:Урок20-006.png |center]]


==Введение==

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

Базы данных применяются для хранения сведений по абонентам, информации по коммутациям и различных системных настроек. В своей работе '''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'''''

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


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

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

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

Чтобы создать таблицу в базе данных '''Oktell''':
* Перейдите в модуль "Проекты". Нажмите '''Добавить'''. Создайте проект.
* Перейдите в модуль "Таблицы абонентов". Нажмите '''Добавить'''. 


<center>[[Файл:Урок20-001.png |500px]] [[Файл:Урок20-002.png |500px]]</center>


* На вкладке "'''Подключение'''" введите название таблицы. В примере название таблицы "'''Lesson20'''". Нажмите "'''Далее'''". 
* На вкладке "'''Данные'''" добавьте столбцы для хранения данных. В примере добавлены следующие столбцы:
:*'''CallerId''' - поле для хранения телефона абонента. Тип Nvarchar(auto)
:*'''CalledId''' - поле для хранения набранного номера. Тип Nvarchar(auto)
:*'''Datetime''' - поле для хранения времени звонка. Тип Datetime
* Нажмите "'''Сохранить'''". 


<center>[[Файл:Урок20-003.png |500px]] [[Файл:Урок20-004.png |500px]]</center>


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


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

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

Программа доступна доступна на сайте [http://www.microsoft.com/  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''' и к ней можно будет обращаться в сценариях. 


[[Файл:Урок20-005.png |center|600px]]


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

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

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


[[Файл:Урок20-007.png |center]]


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

*<u>Компонент "'''Данные абонента'''".</u> Определяются основные параметры звонка - время, номер абонента и набранный номер. 
:* Переменной '''Datetime''' (дата/время) присваивается значение функции "'''Текущие дата и время'''"
:* Переменной '''CalledId''' (строковая) присваивается значение функции "'''Внешний номер (CalledId)'''"
:* Переменной '''CallerId''' (строковая) присваивается значение функции "'''Номер абонента (CallerId)'''"
:* Переменной '''Line''' (строковая) присваивается значение функции "'''Guid-идентификатор линии'''"


*<u>Компонент "'''INSERT'''".</u> Выполняет 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'''. Более подробно написано в статье [[Подключение внешних БД]]


[[Файл:Урок20-010.png|center|500px]]


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

Для того чтобы указать параметры используются служебные символы. Например, чтобы задать параметр '''x''' используйте следующие выражение: 
:* '''@x''' (для SQL серверов) 
:* '''?''' (для ADO и ODBC подключений)
:* ''':''' (для Oracle)

<span style="color:red">ВНИМАНИЕ: С помощью служебных символов '''?''' и ''':''' можно задать только входные параметры.</span> Выходные параметры записываются сразу в переменные сценария с идентичным названием. Следующий запрос возвращает значения столбцов <span style="color:green">callerid</span> и <span style="color:green">calledid</span> в переменные сценария '''name''' и '''number'''.
 
 select callerid as [name], calledid as number
 from maintable 
 where dtime=?

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


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

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


[[Файл:Урок20-011.png|center|1000px]]


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

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

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

Запрос вносит в таблицу '''Lesson20''' в поля <span style="color:green">callerid</span>, <span style="color:green">calledid</span>, <span style="color:green">datetime</span> параметры '''@callerid''', '''@calledid''' и '''@datetime'''. После этого с помощью служебного слова <span style="color:darkviolet">@@IDENTITY</span> определяется номер введенной строки. 

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

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

На вкладке '''Параметры''' параметрам запроса сопоставляются переменные сценария:
:*@calledid - переменная '''calledid'''
:*@callerid - переменная '''callerid'''
:*@datetime - переменная '''Datetime'''
:*@id - переменная '''id'''. 


[[Файл:Урок20-012.png |center|1000px]]


*<u>Компонент "'''На группу'''".</u> Переключает абонента на групповой номер. 
:* '''Назначение''': Внутрь
:* '''Номер''': 105 
:* '''Тип вызова''': Обычный 

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


*<u>Компонент "'''Скоммутированная линия'''"</u>. Определяет номер скоммутированной линии. Используется компонент "'''Статус объекта'''". 

Компонент "'''Статус объекта'''" - это многофункциональный компонент сценанриев, с помощью которого возможно произвести различные действия над основными объектами системы (пользователь, линия, задача, номер и др.). Имеет множество функций, полный перечень можно найти в статье: [[Общие_компоненты_сценариев#.D0.A1.D1.82.D0.B0.D1.82.D1.83.D1.81_.D0.BE.D0.B1.D1.8A.D0.B5.D0.BA.D1.82.D0.B0 | Общие компоненты сценариев: Статус объекта]]

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


[[Файл:Урок20-013.png|center|900px]]



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

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


*<u>Компонент "'''Отладочное уведомление'''"</u>. Выводит значения основных переменных администратору.
 [Line]+endline+[Chained]+endline+[IdUser]+endline+[id]


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

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

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


[[Файл:Урок20-014.png|center|600px]]


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

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


[[Файл:Урок20-015.png|center]]


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


'''Скачать сценарий:''' [http://wiki.oktell.ru/images/c/c0/%D0%A3%D1%80%D0%BE%D0%BA20.zip Урок20.zip]

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

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


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

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

{|cellpadding="10" cellspacing="0" border="0"
| [[Уроки | Наверх]]
| [[Урок 19 Обработка неудачного соединения | К предыдущему уроку]]
| [[Урок 21 Web-запросы | К следующему уроку]]
|-
|}