Reportes: Plantilla para reportes contemplando bases históricas

De Vocalcom.latam Wiki
Saltar a: navegación, buscar
-------------- 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