Если Вы используете Oktell IP PBX без модуля КЦ и Вы не умеете разрабатывать плагины, а необходимо быстро решить вопрос: При входящем звонке отобразить историю всех звонков по номеру с комментариями. Как это сделать? Пойдем через веб-страничку 🙂
Для начало разработаем SQL запрос, что бы увидеть все входящие, исходящие и пропущенные звонки по номеру:
SELECT [s].[TimeAnswer] as [TimeAnswer]
,CONVERT(varchar(max), Cast (Cast ( [s].TimeStop - [s].TimeAnswer as float ) * 1440 as decimal(15,2))) as [TimeLong]
,'входящий' AS [D]
,CONVERT(varchar(max), [s].[Astr]) as [Abonent]
,CONVERT(varchar(max), [s].[Bstr]) as [Oper]
,l.LineCode as [LineCode]
,CONVERT(varchar(max), [c].[Comment]) AS [Comment]
FROM [oktell].[dbo].[A_Stat_Comments] [c]
right outer join [oktell].[dbo].[A_Stat_Connections_1x1] [s] on [s].[Id] = c.[id]
join [oktell].[dbo].A_ServerExtLines [l] on [s].ALineId = [l].id
WHERE ([s].[AOutNumber] like '$c' AND [s].[Bstr] not like 'IVR')
UNION
SELECT [s].[TimeAnswer] as [TimeAnswer]
,CONVERT(varchar(max), Cast (Cast ( [s].TimeStop - [s].TimeAnswer as float ) * 1440 as decimal(15,2))) as [TimeLong]
,'исходящий' AS [D]
,CONVERT(varchar(max), [s].[Bstr]) as [Abonent]
,CONVERT(varchar(max), [s].[Astr]) as [Oper]
,l.LineCode as [LineCode]
,CONVERT(varchar(max), [c].[Comment]) AS [Comment]
FROM [oktell].[dbo].[A_Stat_Comments] [c]
right outer join [oktell].[dbo].[A_Stat_Connections_1x1] [s] on [s].[Id] = c.[id]
join [oktell].[dbo].A_ServerExtLines [l] on [s].BLineId = [l].id
WHERE [s].[BOutNumber] like '$c' AND [s].[Astr] not like 'IVR'
UNION
SELECT [s].[TimeAnswer] as [TimeAnswer]
,CONVERT(varchar(max), Cast (Cast ( [s].TimeStop - [s].TimeAnswer as float ) * 1440 as decimal(15,2))) as [TimeLong]
,'пропущенный' AS [D]
,CONVERT(varchar(max), [s].[Astr]) as [Abonent]
,CONVERT(varchar(max), [s].[Bstr]) as [Oper]
,l.LineCode as [LineCode]
,CONVERT(varchar(max), [c].[Comment]) AS [Comment]
FROM [oktell].[dbo].[A_Stat_Comments] [c]
right outer join [oktell].[dbo].[A_Stat_Connections_1x1] [s] on [s].[Id] = c.[id]
join [oktell].[dbo].A_ServerExtLines [l] on [s].ALineId = [l].id
WHERE [s].[AOutNumber] like '$c' AND [s].[Bstr] like 'IVR' AND [s].[ReasonStop] = '3'
ORDER BY [TimeAnswer] DESC
