TEXT 15
Untitled Guest on 27th July 2025 10:04:57 AM
  1. USE [billing]
  2. GO
  3.  
  4. /****** Object:  StoredProcedure [dbo].[spReportInboundCalls2]    Script Date: 7/27/2025 ******/
  5. SET ANSI_NULLS ON
  6. GO
  7. SET QUOTED_IDENTIFIER ON
  8. GO
  9. -- =============================================
  10. -- Author:      <mokarian>
  11. -- Alter date:  <1401/05/09>
  12. -- Description: <GetTotalInboundCalls - inbound calls only & cleaned filters>
  13. -- =============================================
  14. ALTER PROCEDURE [dbo].[spReportInboundCalls2]
  15.     @Startdate   nvarchar(50),
  16.     @EndDate     nvarchar(50),
  17.     @ext         nvarchar(max),
  18.     @trunk       nvarchar(max),
  19.     @mnumber     nvarchar(50),
  20.     @pbx         nvarchar(max),
  21.     @group       nvarchar(max),
  22.     @calltype    nvarchar(50),
  23.     @status      nvarchar(50)
  24. AS
  25. BEGIN
  26.     SET NOCOUNT ON;
  27.  
  28.     DECLARE @pulse INT;
  29.     SELECT @pulse = [SettingValue]
  30.     FROM [dbo].[Setting]
  31.     WHERE [SettingName] = 'pulse';
  32.  
  33.     SELECT
  34.         COUNT(*) OVER() AS TotalCount,
  35.         ROW_NUMBER() OVER(ORDER BY ci.id DESC) AS [Number],
  36.         dbo.miladitoshamsi(year + mon + day) AS shamsi,
  37.         stime,
  38.         -- Total Call Duration
  39.         RIGHT(CAST(ISNULL(SUM((hrs*3600) + (mins*60) + sec) OVER() / 3600, 0) AS VARCHAR), 10)
  40.         + ':' + RIGHT(CAST((ISNULL(SUM((hrs*3600) + (mins*60) + sec) OVER(), 0) / 60) % 60 AS VARCHAR), 10)
  41.         + ':' + RIGHT(CAST(ISNULL(SUM((hrs*3600) + (mins*60) + sec) OVER(), 0) % 60 AS VARCHAR), 10) AS [Total Call Duration],
  42.         dbo.miladitoshamsi(@Startdate) AS sdate,
  43.         dbo.miladitoshamsi(@EndDate) AS edate,
  44.         -- Raw call duration
  45.         CASE
  46.             WHEN time_to_answer = '****' OR time_to_answer = '    ' THEN '00:00:00'
  47.             ELSE hrs + ':' + mins + ':' + sec
  48.         END AS duration,
  49.         sysname,
  50.         -- Call answered status code
  51.         [dbo].[fun_CallAnsweredFinal](
  52.             TRIM(call_completion_status),
  53.             CASE WHEN (hrs = ' ' AND mins = ' ' AND sec = ' ') THEN '00:00:00' ELSE hrs + ':' + mins + ':' + sec END,
  54.             time_to_answer
  55.         ) AS statusccall,
  56.         status.StatusNamePersian AS statuscall,
  57.         time_to_answer,
  58.         [dbo].[fun_CallTypeName]([dbo].[fun_CallType](ani)) AS call_type,
  59.         -- trunk is where call came in
  60.         trim(callp) AS trunk,
  61.         SUBSTRING(callno, 0, CHARINDEX(' ', callno)) AS callno,
  62.         call_completion_status,
  63.         pc.controllerName,
  64.         g.GroupName,
  65.         -- callee is the internal extension receiving
  66.         trim(callp2) AS callee,
  67.         -- Cleaned time_to_answer in HH:MM:SS
  68.         CASE
  69.             WHEN time_to_answer = '****' OR time_to_answer = '    ' THEN '00:00:00'
  70.             ELSE
  71.                 RIGHT('0' + CAST(ISNULL(TRY_CAST(REPLACE(time_to_answer, '''', '') AS INT), 0) / 3600 AS VARCHAR), 2)
  72.                 + ':' + RIGHT('0' + CAST((ISNULL(TRY_CAST(REPLACE(time_to_answer, '''', '') AS INT), 0) / 60) % 60 AS VARCHAR), 2)
  73.                 + ':' + RIGHT('0' + CAST(ISNULL(TRY_CAST(REPLACE(time_to_answer, '''', '') AS INT), 0) % 60 AS VARCHAR), 2)
  74.         END AS [time_to_answer],
  75.         -- show original callp2 and transfer/ani/callid
  76.         trim(callp2) AS callp2,
  77.         ISNULL(NULLIF(transf, ' '), '--') AS transfer,
  78.         TRIM(ani) AS ani,
  79.         callid
  80.     FROM [Billing].[dbo].[CallInfo] ci
  81.     LEFT JOIN [Billing].[dbo].[pbx-confing] pc
  82.         ON ci.sysid = pc.sysname
  83.     LEFT JOIN [Billing].[dbo].extension ext
  84.         ON trim(ci.callp2) = ext.Extension
  85.     LEFT JOIN [Billing].[dbo].GroupExt gext
  86.         ON ext.Extension = gext.ExtensionId
  87.     LEFT JOIN [Billing].[dbo].Groups g
  88.         ON gext.GroupId = g.Id
  89.     LEFT JOIN CallStatus status
  90.         ON status.StatusCode = [dbo].[fun_CallAnsweredFinal](
  91.             TRIM(ci.call_completion_status),
  92.             CASE WHEN (hrs = ' ' AND mins = ' ' AND sec = ' ') THEN '00:00:00' ELSE hrs + ':' + mins + ':' + sec END,
  93.             TRIM(ci.time_to_answer)
  94.         )
  95.     WHERE
  96.         -- Only inbound calls: callp2 (callee) must be a valid extension
  97.         trim(ci.callp2) <> ''
  98.         AND trim(ci.callp2) NOT LIKE 'T%'
  99.         AND trim(ci.callp2) NOT LIKE 'X%'
  100.         -- Status filter
  101.         AND (
  102.             @status = '-1' OR status.StatusCode IN (SELECT * FROM [dbo].[splitExtension](@status))
  103.         )
  104.         -- Extension filter: only on callp2
  105.         AND (
  106.             @ext = '-1' OR trim(ci.callp2) IN (SELECT * FROM [dbo].[splitExtension](@ext))
  107.         )
  108.         -- Trunk filter
  109.         AND (
  110.             @trunk = '-1' OR trim(ci.callp) IN (SELECT * FROM dbo.splitTrunk(@trunk))
  111.         )
  112.         -- PBX filter
  113.         AND (
  114.             @pbx = '-1' OR ci.sysid IN (SELECT * FROM [dbo].[splitExtension](@pbx))
  115.         )
  116.         -- Call type filter
  117.         AND (
  118.             @calltype = '-1' OR [dbo].[fun_CallType](ci.ani) IN (SELECT * FROM [dbo].[splitExtension](@calltype))
  119.         )
  120.         -- Group filter
  121.         AND (
  122.             @group = '-1' OR g.Id IN (SELECT * FROM [dbo].[splitExtension](@group))
  123.         )
  124.         -- Mnumber filter
  125.         AND SUBSTRING(callno, CHARINDEX(' ', callno) + 1, LEN(callno))
  126.             = IIF(@mnumber = '-1', SUBSTRING(callno, CHARINDEX(' ', callno) + 1, LEN(callno)), @mnumber)
  127.         -- Filter out blank or invalid ani
  128.         AND ci.ani IS NOT NULL
  129.         -- Date range filter
  130.         AND TRY_CAST((year + '-' + mon + '-' + day) AS date)
  131.             BETWEEN @Startdate AND @EndDate
  132.     ORDER BY ci.id DESC;
  133. END
  134. GO

Hightechrobo bin is for source code and general debugging text.

Login or Register to edit, delete and keep track of your pastes and more.

Raw Paste

Login or Register to edit or fork this paste. It's free.