- USE [billing]
- GO
- /****** Object: StoredProcedure [dbo].[spReportInboundCalls2] Script Date: 7/27/2025 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- -- =============================================
- -- Author: <mokarian>
- -- Alter date: <1401/05/09>
- -- Description: <GetTotalInboundCalls - inbound calls only & cleaned filters>
- -- =============================================
- ALTER PROCEDURE [dbo].[spReportInboundCalls2]
- @Startdate nvarchar(50),
- @EndDate nvarchar(50),
- @ext nvarchar(max),
- @trunk nvarchar(max),
- @mnumber nvarchar(50),
- @pbx nvarchar(max),
- @group nvarchar(max),
- @calltype nvarchar(50),
- @status nvarchar(50)
- AS
- BEGIN
- SET NOCOUNT ON;
- DECLARE @pulse INT;
- SELECT @pulse = [SettingValue]
- FROM [dbo].[Setting]
- WHERE [SettingName] = 'pulse';
- SELECT
- COUNT(*) OVER() AS TotalCount,
- ROW_NUMBER() OVER(ORDER BY ci.id DESC) AS [Number],
- dbo.miladitoshamsi(year + mon + day) AS shamsi,
- stime,
- -- Total Call Duration
- RIGHT(CAST(ISNULL(SUM((hrs*3600) + (mins*60) + sec) OVER() / 3600, 0) AS VARCHAR), 10)
- + ':' + RIGHT(CAST((ISNULL(SUM((hrs*3600) + (mins*60) + sec) OVER(), 0) / 60) % 60 AS VARCHAR), 10)
- + ':' + RIGHT(CAST(ISNULL(SUM((hrs*3600) + (mins*60) + sec) OVER(), 0) % 60 AS VARCHAR), 10) AS [Total Call Duration],
- dbo.miladitoshamsi(@Startdate) AS sdate,
- dbo.miladitoshamsi(@EndDate) AS edate,
- -- Raw call duration
- CASE
- WHEN time_to_answer = '****' OR time_to_answer = ' ' THEN '00:00:00'
- ELSE hrs + ':' + mins + ':' + sec
- END AS duration,
- sysname,
- -- Call answered status code
- [dbo].[fun_CallAnsweredFinal](
- TRIM(call_completion_status),
- CASE WHEN (hrs = ' ' AND mins = ' ' AND sec = ' ') THEN '00:00:00' ELSE hrs + ':' + mins + ':' + sec END,
- time_to_answer
- ) AS statusccall,
- status.StatusNamePersian AS statuscall,
- time_to_answer,
- [dbo].[fun_CallTypeName]([dbo].[fun_CallType](ani)) AS call_type,
- -- trunk is where call came in
- trim(callp) AS trunk,
- SUBSTRING(callno, 0, CHARINDEX(' ', callno)) AS callno,
- call_completion_status,
- pc.controllerName,
- g.GroupName,
- -- callee is the internal extension receiving
- trim(callp2) AS callee,
- -- Cleaned time_to_answer in HH:MM:SS
- CASE
- WHEN time_to_answer = '****' OR time_to_answer = ' ' THEN '00:00:00'
- ELSE
- RIGHT('0' + CAST(ISNULL(TRY_CAST(REPLACE(time_to_answer, '''', '') AS INT), 0) / 3600 AS VARCHAR), 2)
- + ':' + RIGHT('0' + CAST((ISNULL(TRY_CAST(REPLACE(time_to_answer, '''', '') AS INT), 0) / 60) % 60 AS VARCHAR), 2)
- + ':' + RIGHT('0' + CAST(ISNULL(TRY_CAST(REPLACE(time_to_answer, '''', '') AS INT), 0) % 60 AS VARCHAR), 2)
- END AS [time_to_answer],
- -- show original callp2 and transfer/ani/callid
- trim(callp2) AS callp2,
- ISNULL(NULLIF(transf, ' '), '--') AS transfer,
- TRIM(ani) AS ani,
- callid
- FROM [Billing].[dbo].[CallInfo] ci
- LEFT JOIN [Billing].[dbo].[pbx-confing] pc
- ON ci.sysid = pc.sysname
- LEFT JOIN [Billing].[dbo].extension ext
- ON trim(ci.callp2) = ext.Extension
- LEFT JOIN [Billing].[dbo].GroupExt gext
- ON ext.Extension = gext.ExtensionId
- LEFT JOIN [Billing].[dbo].Groups g
- ON gext.GroupId = g.Id
- LEFT JOIN CallStatus status
- ON status.StatusCode = [dbo].[fun_CallAnsweredFinal](
- TRIM(ci.call_completion_status),
- CASE WHEN (hrs = ' ' AND mins = ' ' AND sec = ' ') THEN '00:00:00' ELSE hrs + ':' + mins + ':' + sec END,
- TRIM(ci.time_to_answer)
- )
- WHERE
- -- Only inbound calls: callp2 (callee) must be a valid extension
- trim(ci.callp2) <> ''
- AND trim(ci.callp2) NOT LIKE 'T%'
- AND trim(ci.callp2) NOT LIKE 'X%'
- -- Status filter
- AND (
- @status = '-1' OR status.StatusCode IN (SELECT * FROM [dbo].[splitExtension](@status))
- )
- -- Extension filter: only on callp2
- AND (
- @ext = '-1' OR trim(ci.callp2) IN (SELECT * FROM [dbo].[splitExtension](@ext))
- )
- -- Trunk filter
- AND (
- @trunk = '-1' OR trim(ci.callp) IN (SELECT * FROM dbo.splitTrunk(@trunk))
- )
- -- PBX filter
- AND (
- @pbx = '-1' OR ci.sysid IN (SELECT * FROM [dbo].[splitExtension](@pbx))
- )
- -- Call type filter
- AND (
- @calltype = '-1' OR [dbo].[fun_CallType](ci.ani) IN (SELECT * FROM [dbo].[splitExtension](@calltype))
- )
- -- Group filter
- AND (
- @group = '-1' OR g.Id IN (SELECT * FROM [dbo].[splitExtension](@group))
- )
- -- Mnumber filter
- AND SUBSTRING(callno, CHARINDEX(' ', callno) + 1, LEN(callno))
- = IIF(@mnumber = '-1', SUBSTRING(callno, CHARINDEX(' ', callno) + 1, LEN(callno)), @mnumber)
- -- Filter out blank or invalid ani
- AND ci.ani IS NOT NULL
- -- Date range filter
- AND TRY_CAST((year + '-' + mon + '-' + day) AS date)
- BETWEEN @Startdate AND @EndDate
- ORDER BY ci.id DESC;
- END
- GO
Recent Pastes