Reportes: Plantilla para reportes contemplando bases históricas
De Vocalcom.latam Wiki
-------------- DEBUG VARS --------------
DECLARE @CUSTOMERID INT = 2
DECLARE @FECHADESDE DATETIME = '20190101'
DECLARE @FECHAHASTA DATETIME = '20190131'
------------ FIN DEBUG VARS ------------
/**
DEFINIMOS VARIABLES A USAR
*/
DECLARE @RANDOM VARCHAR(12) = CAST(CAST(RAND() * 1000000000 AS INT) AS VARCHAR)
DECLARE @QUERY NVARCHAR(MAX)
/**
GENERALMENTE EN UN REPORTE CON FECHA DESDE Y HASTA SIN FILTRO DE HORAS
SE CONSIDERA LA FECHA HASTA COMO INCLUSIVE, POR LO QUE LE SUMAMOS UN DIA
*/
SET @FECHAHASTA = DATEADD(DAY, 1, @FECHAHASTA)
/**
CREAMOS UNA TABLA VARIABLE PARA CONTENER LA LISTA DE TABLAS QUE VAMOS USAR
*/
DECLARE @QUERY_LIST TABLE
(
ReportType VARCHAR(50),
TableName VARCHAR(200)
)
/**
INSERTAMOS EN LA TABLA ANTERIORMENTE CREADA LAS TABLAS QUE OBTENEMOS DE QUERYLIST, REPETIMOS POR CADA TIPO DE REPORTE QUE NECESITEMOS
*/
INSERT INTO @QUERY_LIST (ReportType, TableName)
SELECT ReportType, REPLACE(ViewName, 'VWAGENTSACTIONS', 'ODActions')
FROM HN_Reporting.dbo.QueryList
WHERE CustomerID = @CUSTOMERID
AND MinTime <= CONVERT(CHAR(8), @FECHAHASTA, 112) + '000000'
AND MaxTime >= CONVERT(CHAR(8), @FECHADESDE, 112) + '000000'
AND ReportType = 'AGENT'
INSERT INTO @QUERY_LIST (ReportType, TableName)
SELECT ReportType, REPLACE(ViewName, 'VWINBOUNDCALLS', 'ODCalls')
FROM HN_Reporting.dbo.QueryList
WHERE CustomerID = @CUSTOMERID
AND MinTime <= CONVERT(CHAR(8), @FECHAHASTA, 112) + '000000'
AND MaxTime >= CONVERT(CHAR(8), @FECHADESDE, 112) + '000000'
AND ReportType = 'INBOUND'
INSERT INTO @QUERY_LIST (ReportType, TableName)
SELECT 'IVR', REPLACE(ViewName, 'VWINBOUNDCALLS', 'ODIVR')
FROM HN_Reporting.dbo.QueryList
WHERE CustomerID = @CUSTOMERID
AND MinTime <= CONVERT(CHAR(8), @FECHAHASTA, 112) + '000000'
AND MaxTime >= CONVERT(CHAR(8), @FECHADESDE, 112) + '000000'
AND ReportType = 'INBOUND'
/**
CREAMOS TABLAS TEMPORALES PARA CONTENER LOS REGISTROS DE CADA TABLA DE ODCALLS Y ODACTIONS QUE VAMOS A UTILIZAR
*/
SET @QUERY = 'CREATE TABLE ##ODActions_{RANDOM} (
ID CHAR(32),
ActionUniversalTime DATETIME,
ActionLocalTime DATETIME,
ActionUniversalTimeString VARCHAR(14),
ActionLocalTimeString VARCHAR(14),
CustomerId INT,
AgentId VARCHAR(32),
Context INT,
SessionId CHAR(32),
State INT,
StateDetail INT,
Duration INT,
Campaign VARCHAR(255),
ChannelId INT,
NextState INT
)'
SET @QUERY = REPLACE(@QUERY, '{RANDOM}', @RANDOM)
EXEC sp_executesql @QUERY
SET @QUERY = 'CREATE TABLE ##ODCalls_{RANDOM} (
ID CHAR(32),
CtiID VARCHAR(60),
CustomerID INT,
Indice NUMERIC(18),
CallType INT,
CallUniversalTime DATETIME,
CallLocalTime DATETIME,
CallUniversalTimeString VARCHAR(14),
CallLocalTimeString VARCHAR(14),
Duration INT DEFAULT 0,
CallDuration INT DEFAULT 0,
AcceptDuration INT DEFAULT 0,
IvrDuration INT DEFAULT 0,
WaitDuration INT DEFAULT 0,
TotalWaitDuration INT DEFAULT 0,
ConvDuration INT DEFAULT 0,
WrapupDuration INT DEFAULT 0,
RerouteDuration INT DEFAULT 0,
OverflowDuration INT DEFAULT 0,
ANI VARCHAR(60),
DNIS VARCHAR(60),
FirstCampaign VARCHAR(255),
FirstVirtualCamp VARCHAR(255),
LastCampaign VARCHAR(255),
LastVirtualCamp VARCHAR(255),
UUI VARCHAR(255),
Memo NVARCHAR(255),
AssociatedData NVARCHAR(255),
OutTel VARCHAR(60),
OutDialed VARCHAR(100),
Closed INT,
NoAgent INT,
Overflow INT,
Abandon INT,
FirstIVR VARCHAR(255),
LastIVR VARCHAR(255),
FirstQueue INT,
LastQueue INT,
InitPriority INT,
FirstAgent INT,
LastAgent INT,
LastTransfer VARCHAR(60),
CallStatusGroup INT,
CallStatusNum INT,
CallStatusDetail INT,
Comments NVARCHAR(255),
ContactID VARCHAR(50),
EndByAgent INT,
AgentListen INT,
EndReason INT,
RefID VARCHAR(32),
ProActiveReason NVARCHAR(100)
)'
SET @QUERY = REPLACE(@QUERY, '{RANDOM}', @RANDOM)
EXEC sp_executesql @QUERY
SET @QUERY = 'CREATE TABLE ##ODIVR_{RANDOM} (
ID CHAR(32),
UniversalTime DATETIME,
LocalTime DATETIME,
UniversalTimeString VARCHAR(14),
LocalTimeString VARCHAR(14),
CustomerID INT,
IvrId INT,
IvrName VARCHAR(50),
IvrMessage NVARCHAR(250),
Duration INT,
CallID CHAR(32)
)'
SET @QUERY = REPLACE(@QUERY, '{RANDOM}', @RANDOM)
EXEC sp_executesql @QUERY
/**
CREAMOS UN CURSOR EN BASE A LA LISTA DE TABLAS E INSERTAMOS LOS DATOS DE CADA UNA EN LAS TABLAS VARIABLES QUE ACABAMOS DE CREAR
*/
DECLARE @TABLENAME VARCHAR(200)
DECLARE @REPORTTYPE VARCHAR(50)
DECLARE CURSOR_TABLAS CURSOR
FOR
SELECT *
FROM @QUERY_LIST
OPEN CURSOR_TABLAS
FETCH NEXT FROM CURSOR_TABLAS
INTO @REPORTTYPE, @TABLENAME
WHILE @@FETCH_STATUS = 0
BEGIN
IF @REPORTTYPE = 'AGENT' -- INSERTAMOS EN LA TABLA TEMPORAL DE ODACTIONS
BEGIN
SET @QUERY = 'INSERT INTO ##ODActions_{RANDOM} (ID,' +
'ActionUniversalTime,' +
'ActionLocalTime,' +
'ActionUniversalTimeString,' +
'ActionLocalTimeString,' +
'CustomerId,' +
'AgentId,' +
'Context,' +
'SessionId,' +
'State,' +
'StateDetail,' +
'Duration,' +
'Campaign,' +
'ChannelId,' +
'NextState) ' +
'SELECT ID,' +
'ActionUniversalTime,' +
'ActionLocalTime,' +
'ActionUniversalTimeString,' +
'ActionLocalTimeString,' +
'CustomerId,' +
'AgentId,' +
'Context,' +
'SessionId,' +
'State,' +
'StateDetail,' +
'Duration,' +
'Campaign,' +
'ChannelId,' +
'NextState ' +
'FROM {TABLENAME} ' +
'WHERE CustomerId = @CUSTOMERID ' +
'AND ActionLocalTime >= @FECHADESDE ' +
'AND ActionLocalTime < @FECHAHASTA'
SET @QUERY = REPLACE(@QUERY, '{RANDOM}', @RANDOM)
SET @QUERY = REPLACE(@QUERY, '{TABLENAME}', @TABLENAME)
EXEC sp_executesql @QUERY,
N'@CUSTOMERID INT, @FECHADESDE DATETIME, @FECHAHASTA DATETIME',
@CUSTOMERID = @CUSTOMERID, @FECHADESDE = @FECHADESDE, @FECHAHASTA = @FECHAHASTA
END
IF @REPORTTYPE = 'INBOUND' -- INSERTAMOS EN LA TABLA TEMPORAL DE ODCALLS
BEGIN
SET @QUERY = 'INSERT INTO ##ODCalls_{RANDOM} (ID,' +
'CtiID,' +
'CustomerID,' +
'Indice,' +
'CallType,' +
'CallUniversalTime,' +
'CallLocalTime,' +
'CallUniversalTimeString,' +
'CallLocalTimeString,' +
'Duration,' +
'CallDuration,' +
'AcceptDuration,' +
'IvrDuration,' +
'WaitDuration,' +
'TotalWaitDuration,' +
'ConvDuration,' +
'WrapupDuration,' +
'RerouteDuration,' +
'OverflowDuration,' +
'ANI,' +
'DNIS,' +
'FirstCampaign,' +
'FirstVirtualCamp,' +
'LastCampaign,' +
'LastVirtualCamp,' +
'UUI,' +
'Memo,' +
'AssociatedData,' +
'OutTel,' +
'OutDialed,' +
'Closed,' +
'NoAgent,' +
'Overflow,' +
'Abandon,' +
'FirstIVR,' +
'LastIVR,' +
'FirstQueue,' +
'LastQueue,' +
'InitPriority,' +
'FirstAgent,' +
'LastAgent,' +
'LastTransfer,' +
'CallStatusGroup,' +
'CallStatusNum,' +
'CallStatusDetail,' +
'Comments,' +
'ContactID,' +
'EndByAgent,' +
'AgentListen,' +
'EndReason,' +
'RefID,' +
'ProActiveReason) ' +
'SELECT ID,' +
'CtiID,' +
'CustomerID,' +
'Indice,' +
'CallType,' +
'CallUniversalTime,' +
'CallLocalTime,' +
'CallUniversalTimeString,' +
'CallLocalTimeString,' +
'Duration,' +
'CallDuration,' +
'AcceptDuration,' +
'IvrDuration,' +
'WaitDuration,' +
'TotalWaitDuration,' +
'ConvDuration,' +
'WrapupDuration,' +
'RerouteDuration,' +
'OverflowDuration,' +
'ANI,' +
'DNIS,' +
'FirstCampaign,' +
'FirstVirtualCamp,' +
'LastCampaign,' +
'LastVirtualCamp,' +
'UUI,' +
'Memo,' +
'AssociatedData,' +
'OutTel,' +
'OutDialed,' +
'Closed,' +
'NoAgent,' +
'Overflow,' +
'Abandon,' +
'FirstIVR,' +
'LastIVR,' +
'FirstQueue,' +
'LastQueue,' +
'InitPriority,' +
'FirstAgent,' +
'LastAgent,' +
'LastTransfer,' +
'CallStatusGroup,' +
'CallStatusNum,' +
'CallStatusDetail,' +
'Comments,' +
'ContactID,' +
'EndByAgent,' +
'AgentListen,' +
'EndReason,' +
'RefID,' +
'ProActiveReason ' +
'FROM {TABLENAME} ' +
'WHERE CustomerID = @CUSTOMERID ' +
'AND CallLocalTime >= @FECHADESDE ' +
'AND CallLocalTime < @FECHAHASTA'
SET @QUERY = REPLACE(@QUERY, '{RANDOM}', @RANDOM)
SET @QUERY = REPLACE(@QUERY, '{TABLENAME}', @TABLENAME)
EXEC sp_executesql @QUERY,
N'@CUSTOMERID INT, @FECHADESDE DATETIME, @FECHAHASTA DATETIME',
@CUSTOMERID = @CUSTOMERID, @FECHADESDE = @FECHADESDE, @FECHAHASTA = @FECHAHASTA
END
IF @REPORTTYPE = 'IVR' -- INSERTAMOS EN LA TABLA TEMPORAL DE ODIVR
BEGIN
SET @QUERY = 'INSERT INTO ##ODIVR_{RANDOM} (' +
'ID,' +
'UniversalTime,' +
'LocalTime,' +
'UniversalTimeString,' +
'LocalTimeString,' +
'CustomerID,' +
'IvrId,' +
'IvrName,' +
'IvrMessage,' +
'Duration,' +
'CallID ' +
') ' +
'SELECT ' +
'ID,' +
'UniversalTime, ' +
'LocalTime,' +
'UniversalTimeString,' +
'LocalTimeString,' +
'CustomerID,' +
'IvrId,' +
'IvrName,' +
'IvrMessage,' +
'Duration,' +
'CallID ' +
'FROM {TABLENAME} ' +
'WHERE CustomerId = @CUSTOMERID ' +
'AND LocalTime >= @FECHADESDE ' +
'AND LocalTime < @FECHAHASTA'
SET @QUERY = REPLACE(@QUERY, '{RANDOM}', @RANDOM)
SET @QUERY = REPLACE(@QUERY, '{TABLENAME}', @TABLENAME)
EXEC sp_executesql @QUERY,
N'@CUSTOMERID INT, @FECHADESDE DATETIME, @FECHAHASTA DATETIME',
@CUSTOMERID = @CUSTOMERID, @FECHADESDE = @FECHADESDE, @FECHAHASTA = @FECHAHASTA
END
FETCH NEXT FROM CURSOR_TABLAS
INTO @REPORTTYPE, @TABLENAME
END
CLOSE CURSOR_TABLAS
DEALLOCATE CURSOR_TABLAS
/**
LUEGO GENERAMOS NUESTRO REPORTE EN BASE A LAS TABLAS TEMPORALES DE ODCALLS Y ODACTIONS
*/
SET @QUERY = 'SELECT * FROM ##ODActions_{RANDOM}'
SET @QUERY = REPLACE(@QUERY, '{RANDOM}', @RANDOM)
EXEC sp_executesql @QUERY
SET @QUERY = 'SELECT * FROM ##ODCalls_{RANDOM}'
SET @QUERY = REPLACE(@QUERY, '{RANDOM}', @RANDOM)
EXEC sp_executesql @QUERY
SET @QUERY = 'SELECT * FROM ##ODIVR_{RANDOM}'
SET @QUERY = REPLACE(@QUERY, '{RANDOM}', @RANDOM)
EXEC sp_executesql @QUERY
/**
BORRAMOS TODAS LAS TABLAS TEMPORALES USADAS
*/
SET @QUERY = 'DROP TABLE ##ODActions_{RANDOM}'
SET @QUERY = REPLACE(@QUERY, '{RANDOM}', @RANDOM)
EXEC sp_executesql @QUERY
SET @QUERY = 'DROP TABLE ##ODCalls_{RANDOM}'
SET @QUERY = REPLACE(@QUERY, '{RANDOM}', @RANDOM)
EXEC sp_executesql @QUERY
SET @QUERY = 'DROP TABLE ##ODIVR_{RANDOM}'
SET @QUERY = REPLACE(@QUERY, '{RANDOM}', @RANDOM)
EXEC sp_executesql @QUERY
