Reportes: Plantilla para reportes contemplando bases históricas

De Vocalcom.latam Wiki
Saltar a: navegación, buscar
-------------- DEBUG VARS --------------
DECLARE @CUSTOMERID INT = 2
DECLARE @FECHA DATETIME = '20171026'
DECLARE @FECHAHASTA DATETIME = '20171026'
DECLARE @ANIO INT = 2017
DECLARE @MES INT = 101
------------ FIN DEBUG VARS ------------

/**
DEFINIMOS VARIABLES A USAR
 */
DECLARE @RANDOM VARCHAR(12) = CAST( CAST( RAND() * 1000000000 AS INT ) AS VARCHAR )
DECLARE @QUERY NVARCHAR(MAX)

/**
CREAMOS UNA TABLA VARIABLE PARA CONTENER LA LISTA DE TABLAS QUE VAMOS USAR
 */
DECLARE @QUERY_LIST TABLE (ReportType VARCHAR(50), TableName VARCHAR(50))

/**
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), @FECHA, 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), @FECHA, 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), @FECHA, 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
)'

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)
)'

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)
)'

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 = ' + CAST(@CUSTOMERID AS VARCHAR) + ' ' +
                     'AND CONVERT(CHAR(8), ActionLocalTime, 112) >= ''' + CONVERT(CHAR(8), @FECHA, 112) + ''' ' +
                     'AND CONVERT(CHAR(8), ActionLocalTime, 112) <= ''' + CONVERT(CHAR(8), @FECHAHASTA, 112) + ''''

        EXEC sp_executesql @QUERY

      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 = ' + CAST(@CUSTOMERID AS VARCHAR) + ' ' +
                     'AND CONVERT(CHAR(8), CallLocalTime, 112) >= ''' + CONVERT(CHAR(8), @FECHA, 112) + ''' ' +
                     'AND CONVERT(CHAR(8), CallLocalTime, 112) <= ''' + CONVERT(CHAR(8), @FECHAHASTA, 112) + ''''

        EXEC sp_executesql @QUERY

      END
    IF @REPORTTYPE = 'IVR' -- INSERTAMOS EN LA TABLA TEMPORAL DE ODACTIONS
      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 = ' + CAST(@CUSTOMERID AS VARCHAR) + ' ' +
                     'AND CONVERT(CHAR(8), LocalTime, 112) >= ''' + CONVERT(CHAR(8), @FECHA, 112) + ''' ' +
                     'AND CONVERT(CHAR(8), LocalTime, 112) <= ''' + CONVERT(CHAR(8), @FECHAHASTA, 112) + ''''

        EXEC sp_executesql @QUERY

      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
EXEC sp_executesql @QUERY
SET @QUERY = 'SELECT * FROM ##ODCalls_' + @RANDOM
EXEC sp_executesql @QUERY
SET @QUERY = 'SELECT * FROM ##ODIVR_' + @RANDOM
EXEC sp_executesql @QUERY



/**
BORRAMOS TODAS LAS TABLAS TEMPORALES USADAS
 */
SET @QUERY = 'DROP TABLE ##ODActions_' + @RANDOM
EXEC sp_executesql @QUERY
SET @QUERY = 'DROP TABLE ##ODCalls_' + @RANDOM
EXEC sp_executesql @QUERY
SET @QUERY = 'DROP TABLE ##ODIVR_' + @RANDOM
EXEC sp_executesql @QUERY