Reportes por Excepcion

Implementación del concepto de que la información vaya a donde se necesite y no que haya que ir a buscarla.

Se trata de enviar reportes por mail cuando ocurra alguna situación que merezca la intervención de alquien.  Éstos solamente deben llegar si hay algo para informarse ( es decir, NO deben llegar reportes “vacios”)

Ej: Un reporte con las cuentas control con saldo distinto de cero que le llegue diariamente al Responsable administrativo, o un reporte semanal de las cuentas corrientes de se excedieron del límite de crédito

Solucion:

Guardo en tablas de usuarios los reportes que deben ser disparados, juntamente con los parámetros de ejecución y la frecuencia ( diaria, semanal o mensual ).

Un mismo reporte puede ser ejecutado con distintas frecuencias, cambiando los parámetros.

Un Job que levante dichos datos de las tablas usr, genere el reporte en formato html a una determinada ubicación en el disco y lo envíe solamente si el reporte trajo algún registro.

Implementación

TABLAS DE USUARIO

Se una tabla USR_JOBREH con los códigos de los reportes a ejecutarse.  Se definen distintas frecuencias de ejecucion : diaria, semanal y mensual.

Un nuevo tipo de dato que indique la frecuencia

Se crea una tabla hija USR_JOBREI con los parámetros con los cuales se ejecutará el reporte.

Aqui una pantalla con un ejemplo de los datos cargados para un reporte

encabezado:

campos clave

USR_JOBREH_RPTNAM   : código del reporte
USR_JOBREH_NROITM    : frecuencia

demás campos

USR_JOBREH_MAILTO    : listado de direcciones adonde se enviará el mail, separados por punto y coma
USR_JOBREH_ASUNTO    : asunto del mail
USR_JOBREH_FCHEJE    : dato estadístico, fecha de última ejecución
USR_JOBREH_CNTREG    : dato estadístico, cantidad de registros recuperados
USR_JOBREH_DEBAJA    : para deshabilitar transitoriamente el envío del reporte

Tabla hija ( parámetros )

clave:

USR_JOBREI_RPTNAM    : relacion a la tabla madre
USR_JOBREI_NROITM    : relacion a la tabla madre
USR_JOBREI_PANAM    : código de parámetro

atributo

USR_JOBREI_PVALUE    : valor del parámetro

DENTRO DE CADA REPORTE -CONTROL DE REGISTROS RECUPERADOS

Simplemente a través de una variable.

Evento Begin:

VARIABLES.DECLAREVARIABLE(“RE_CONTADOR”)

Campo calculado

VARIABLES(“RE_CONTADOR”)=VARIABLES(“RE_CONTADOR”)+1

ADMINISTRADOR DE TAREAS -JOB QUE ENVÍA LOS REPORTES POR MAILS

Usé un solo job para todo.

Todos los días se disparan los diarios. Si es lunes se disparan los semanales y si además es primer dia del mes se procesan los mensuales.

Se lee la tabla de usuario y se procesan los reportes.

La variable ReportName queda con el código de reporte, observar que hay que pasarlo entre paréntesis para que funcione, sino da “type mistmatch”

Set oReport = oApplication.Companies(CompanyName).GetObject((ReportName), ObjectType, “”)

Luego de ejecutarse se lee la variable con la cantidad de registros traídos

cntreg = OREPORT.REPORTBUSINESS.USERVARIABLESVALUE(“RE_CONTADOR”)

 

'**************** Declaracion de Variables ***********************************************************************************

Public Dbern, Rs, Ri, oReport, oRenderer, ReportName
Const CompanyName = "CODIGODEEMPRESA"
Const ObjectType = 5 'Constante que indica el tipo de objeto. Este vale 5 para los reportes
Const OutputPrinter = 3 'Constante que indica el tipo de salida, esta es 3 para archivo de HTML.

 Conexion
 ProcesoReportes(1) ' Reportes con Frecuencia Diaria

 ' Los días LUNES corro los reportes con Frecuencia semanal
 If weekday(now()) = vbMonday Then
   ProcesoReportes(2) ' Reportes con Frecuencia Semanal
 End If

 ' El PRIMER DIA DEL MES corro los reportes con frecuencia mensual
 If day(now()) = 1 Then
   ProcesoReportes(3) ' Reportes con Frecuencia Mensual
 End If

 cierroconexion

Function Conexion()

Const bIntegratedSecurity = False
DBProperties.CompanyName = CompanyName
sUser = DBProperties.User 'Devuelve el usuario de la base de datos
sPassWord = DBProperties.Password 'Devuelve la password de la base de datos
sDatabase = DBProperties.Database 'Devuelve el nombre de la base de datos
sServer = DBProperties.Server 'Devuelve el nombre del servidor de base de datos
sDriver = DBProperties.Driver 'Devuelve el nombre del driver del servidor de base de datos

Set Dbern = CreateObject("ADODB.Connection")
Dbern.Provider = "sqloledb"
Dbern.Properties("Data Source").Value = sServer
Dbern.Properties("Initial Catalog").Value = sDatabase

'************* Contempla el tipo de seguridad de la base de datos ********************

If bIntegratedSecurity Then 'Para correr con seguridad Integrada
   Dbern.Properties("User ID").Value = ""
   Dbern.Properties("Password").Value = ""
   Dbern.Properties("Integrated Security").Value = "SSPI"
Else
   Dbern.Properties("User ID").Value = sUser
   Dbern.Properties("Password").Value = sPassWord
End If
Dbern.Open

End Function

 Function ProcesoReportes(frecuencia)

 Dim nombrereporte, destinatario, asunto
 SSQL = ""
 SSQL = SSQL & " SELECT USR_JOBREH_RPTNAM, USR_JOBREH_MAILTO, USR_JOBREH_ASUNTO "
 SSQL = SSQL & " FROM USR_JOBREH "
 SSQL = SSQL & " WHERE USR_JOBREH_NROITM = " & frecuencia
 SSQL = SSQL & " AND ISNULL(USR_JOBREH_DEBAJA,'S') = 'N' "

Set Rs = Dbern.Execute(CStr(SSQL))

    While Not Rs.EOF
        nombrereporte = Trim(CStr(Rs("USR_JOBREH_RPTNAM").Value))
        destinatario = Trim(CStr(Rs("USR_JOBREH_MAILTO").Value))
        asunto = Trim(CStr(Rs("USR_JOBREH_ASUNTO").Value))

        ProcesoReporte nombrereporte, destinatario, asunto
        Rs.MoveNext
    Wend

    Rs.Close
    Set Rs = Nothing

End Function

Function ProcesoReporte(ReportName, mailto, asunto)
    Const CompanyName = "ERNSA"
    Const ObjectType = 5 'Constante que indica el tipo de objeto. Este vale 5 para los reportes
    Const OutputPrinter = 3 'Constante que indica el tipo de salida, esta es 3 para archivo de HTML.
    Dim oReport 'Variable Objeto que contendrá el Reporte en si.
    Dim oRenderer 'Variable Objeto que contendrá el componente de pintado del reporte.
    Dim cntreg

    'Creación del Objeto Reporte.

      Set oReport = oApplication.Companies(CompanyName).GetObject((ReportName), ObjectType, "")

      ' Capturo el valor de los parámetros
      SSQL = ""
      SSQL = SSQL & " SELECT  USR_JOBREI_PANAM,USR_JOBREI_PVALUE "
      SSQL = SSQL & " FROM USR_JOBREI "
      SSQL = SSQL & " WHERE USR_JOBREI_RPTNAM = '" & ReportName & "'  "
      SSQL = SSQL & " AND USR_JOBREI_NROITM = 1 "

      Set Ri = Dbern.Execute(CStr(SSQL))

      While Not Ri.EOF
        oReport.Parameters((Ri("USR_JOBREI_PANAM"))).ValueFrom = CStr(Ri("USR_JOBREI_PVALUE"))
        Ri.MoveNext
    Wend

    Ri.Close
    Set Ri = Nothing

    Set oRenderer = oReport.GetRenderer(OutputPrinter) 'Obtiene un objeto Renderer que maneja las opciones de impresora
    'Nombre de Archivo : Path y nombre del archivo HTML generado
    oRenderer.Filename = "C:\Adjuntos\Reportes\" & CStr(ReportName) & ".html"
    'Imprime al archivo HTML
    oRenderer.Render

    'Si el reporte trajo algún registro, lo envío por mail

    cntreg = OREPORT.REPORTBUSINESS.USERVARIABLESVALUE("RE_CONTADOR")

    If cntreg > 0 Then
        ' envio por mail
        SSQL = ""
        SSQL = SSQL & " EXEC msdb.dbo.sp_send_dbmail "
        SSQL = SSQL & " @profile_name = 'SQL', "
        SSQL = SSQL & " @recipients = '" & mailto & "' , "
        SSQL = SSQL & " @blind_copy_recipients = 'pedrantic@rionegro.com.ar' , "
        SSQL = SSQL & " @subject =  '" & asunto & "' , "
        SSQL = SSQL & " @file_attachments = '" & oRenderer.Filename & "' ,"
        SSQL = SSQL & " @append_query_error = 1 "

        Dbern.Execute (CStr(SSQL))
    End If

End Function

Function cierroconexion()

Dbern.Close
Set Dbern = Nothing

End Function

T

CARGAR LAS TABLAS DE USUARIO DESDE EL MISMO REPORTE

Es más cómodo cargar los parámetros de ejecución desde el mismo reporte. Para ello desde los eventos cargo los parámetros guardados en las tablas de usuario, y si se le pone el check “actualizo datos en job”, se actualizan los valores en la USR_JOBREH/I

LOS PARAMETROS DEL REPORTE AQUI ( Observar los que empiezan con JOB_ )

ENTONCES, AQUI EL CÓDIGO COMPLETO DE LOS EVENTOS EN LOS REPORTES

Initialize

VARIABLES.DECLAREVARIABLE("RE_CONTADOR")
DIM Ri
dim nroitm,mailto,asunto,debaja

' ***********************cambiar aqui segun corresponda*************************
nroitm=1 'FRECUENCIA  

' ********************************************************************************

Set oconn = createobject("ADODB.Connection")
oconn.ConnectionString = "Provider=SQLNCLI;" _
         & "Server=SERVIDOR;" _
         & "Database=EMP_XXX;" _
         & "Integrated Security=SSPI;" _
         & "DataTypeCompatibility=80;" _
         & "MARS Connection=True;"

oConn.Open

' Recupero el valor de los parámetros
SSQL=""
SSQL = SSQL & " SELECT  USR_JOBREI_PANAM,USR_JOBREI_PVALUE "
SSQL = SSQL & " FROM USR_JOBREI "
SSQL = SSQL & " WHERE USR_JOBREI_RPTNAM = '"& ReportName & "'  "
SSQL = SSQL & " AND USR_JOBREI_NROITM = " & nroitm

Set Ri = oConn.Execute(cstr(SSQL))

While Not Ri.EOF
  Parameters((Ri("USR_JOBREI_PANAM"))).ValueFrom = cstr(Ri("USR_JOBREI_PVALUE"))
  Ri.MoveNext
Wend	

oConn.Close

' Recupero el valor del parámetro mailto

SSQL = ""
SSQL = SSQL & " SELECT USR_JOBREH_MAILTO "
SSQL = SSQL & " FROM USR_JOBREH "
SSQL = SSQL & " WHERE USR_JOBREH_RPTNAM = '"& ReportName & "'  "
SSQL = SSQL & " AND USR_JOBREH_NROITM = " & nroitm

mailto = QUERYEXEC(CSTR(SSQL))

SSQL = ""
SSQL = SSQL & " SELECT USR_JOBREH_ASUNTO "
SSQL = SSQL & " FROM USR_JOBREH "
SSQL = SSQL & " WHERE USR_JOBREH_RPTNAM = '"& ReportName & "'  "
SSQL = SSQL & " AND USR_JOBREH_NROITM = " & nroitm

asunto = QUERYEXEC(CSTR(SSQL))

SSQL = ""
SSQL = SSQL & " SELECT USR_JOBREH_DEBAJA "
SSQL = SSQL & " FROM USR_JOBREH "
SSQL = SSQL & " WHERE USR_JOBREH_RPTNAM = '"& ReportName & "'  "
SSQL = SSQL & " AND USR_JOBREH_NROITM = " & nroitm

debaja = QUERYEXEC(CSTR(SSQL))

Parameters("JOB_MAILTO").ValueFrom = mailto
Parameters("JOB_ASUNTO").ValueFrom = asunto
Parameters("JOB_NROITM").ValueFrom = nroitm
Parameters("JOB_DEBAJA").ValueFrom = debaja

EVENTOBEGIN

VARIABLES("RE_CONTADOR")=0

EVENTO END

' Actualizo la cantidad de registros traídos por el reporte

CNTREG = VARIABLES("RE_CONTADOR")

SSQL = ""
SSQL = SSQL & " UPDATE USR_JOBREH "
SSQL = SSQL & " SET USR_JOBREH_FCHEJE = GETDATE() , "
SSQL = SSQL & " USR_JOBREH_CNTREG = " & CNTREG
SSQL = SSQL & " WHERE USR_JOBREH_RPTNAM =  '" & reportname & "' "
QUERYEXEC(CSTR(SSQL))

' Código Utilizado en los Reportes por Excepción
Dim ParamFijos
ParamFijos = Array("JOB_ACTU","JOB_MAILTO","JOB_ASUNTO","JOB_NROITM","JOB_DEBAJA")

' Borro Registros e Inserto Header

If Parameters("JOB_ACTU").Values(1).fromValueVariant = "S" THEN
   SSQL = ""
   SSQL = SSQL & " Usr_Sp_JOBREHI_Delete "
   SSQL = SSQL & " '" & reportname & "' ,"
   SSQL = SSQL & " "& PARAMETERS("JOB_NROITM").VALUES(1).FROMVALUEVARIANT & "  "

   ' Borro Registros
   QUERYEXEC(CSTR(SSQL)) 

   ' Inserto Header

   SSQL = ""
   SSQL = SSQL & " USR_SP_JOBREH_INSERT "
   SSQL = SSQL & " '" & reportname & "' ,"
   SSQL = SSQL & " "& PARAMETERS("JOB_NROITM").VALUES(1).FROMVALUEVARIANT & ",  "
   SSQL = SSQL & " '"& PARAMETERS("JOB_MAILTO").VALUES(1).FROMVALUEVARIANT & "' ,"
   SSQL = SSQL & " '"& PARAMETERS("JOB_ASUNTO").VALUES(1).FROMVALUEVARIANT & "' ,"
   SSQL = SSQL & " '"& PARAMETERS("JOB_DEBAJA").VALUES(1).FROMVALUEVARIANT & "' " 

   QUERYEXEC(CSTR(SSQL))

   ' Inserto Items

  For i = 1 To Parameters.Count
      If Not EsFijo(Parameters(i).Name) Then
          SSQL = ""
          SSQL = SSQL & " Usr_Sp_JOBREI_Insert "
          SSQL = SSQL & " '" & reportname & "' ,"
          SSQL = SSQL & " "& PARAMETERS("JOB_NROITM").VALUES(1).FROMVALUEVARIANT & " , "
          SSQL = SSQL & " '"& Parameters(i).Name &"' ,"
          SSQL = SSQL & " '"& Parameters(i).Values(1).FromValueVariant &"' "

          QUERYEXEC(CSTR(SSQL))
      End if
   Next

END IF

Function EsFijo(par_nombre)

EsFijo = False
For t = 0 To UBound(ParamFijos)
If par_nombre = ParamFijos(t) Then
EsFijo = True
Exit For
End If
Next

End Function

EVENTO TERMINATE

' Actualizo la cantidad de registros traídos por el reporte

CNTREG = VARIABLES("RE_CONTADOR")

SSQL = ""
SSQL = SSQL & " UPDATE USR_JOBREH "
SSQL = SSQL & " SET USR_JOBREH_FCHEJE = GETDATE() , "
SSQL = SSQL & " USR_JOBREH_CNTREG = " & CNTREG
SSQL = SSQL & " WHERE USR_JOBREH_RPTNAM =  '" & reportname & "' "
QUERYEXEC(CSTR(SSQL))

AQUI LOS DOS STORED PROCEDURES QUE HAY QUE CREAR EN EL SERVIDOR

CREATE PROCEDURE Usr_Sp_JOBREH_Insert
( @RPTNAM varchar(15),@NROITM INTEGER,@MAILTO text,@ASUNTO text,@DEBAJA CHAR(1))

/*
APLICACION: ACTUALIZACION VALORES EN JOB DE REPORTES
DESARROLLADO POR: CARLOS L. PEDRANTI
ULTIMA MODIFICACION: 12/08/2008

Usr_Sp_JOBREH_Insert 'USR_PPPRUEBA',1,'pedrantic@rionegro.com.ar','ASUNTO PRUEBA','N'

*/
AS
SET NOCOUNT ON

DECLARE @error_stat int,
        @return_stat int,
        @row_count int,
        @msgerror varchar(255)

BEGIN TRAN
	INSERT INTO USR_JOBREH
	( USR_JOBREH_RPTNAM,
	USR_JOBREH_NROITM,
	USR_JOBREH_MAILTO,
	USR_JOBREH_ASUNTO,
	USR_JOBREH_DEBAJA,
	USR_JO_FECALT,
	USR_JO_FECMOD,
	USR_JO_USERID,
	USR_JO_ULTOPR,
	USR_JO_DEBAJA,
	USR_JO_OALIAS)

	SELECT
	@RPTNAM USR_JOBREH_RPTNAM,
	@NROITM USR_JOBREH_NROITM,
	@MAILTO USR_JOBREH_MAILTO,
	@ASUNTO USR_JOBREH_ASUNTO,
	@DEBAJA,
	GETDATE() USR_JO_FECALT,
	GETDATE() USR_JO_FECMOD,
	'PROCESO' USR_JO_USERID,
	'A' USR_JO_ULTOPR,
	'N' USR_JO_DEBAJA,
	'USR_JOBREH' USR_JO_OALIAS

	SELECT  @error_stat = @@ERROR,
        @row_count  = @@ROWCOUNT       

	IF @error_stat <> 0
	BEGIN
		ROLLBACK TRAN
		Select @msgerror=
		'@?@2#USR_JOBREH#' +
		'#****Error Insertando en USR_JOBREH**#'
		RAISERROR(@msgerror, 16, 1)
		RETURN  @error_stat
	END

IF @row_count <> 1
BEGIN
   ROLLBACK TRAN
   Select @msgerror=
   '@?@2#USR_JOBREH#' +
   '#******Cant de valores actualizados <> 1 en Usr_Sp_JOBREH_Insert*****#'
   RAISERROR(@msgerror, 16, 1)
   RETURN -999
END
COMMIT TRAN
RETURN 0      

CREATE PROCEDURE Usr_Sp_JOBREI_Insert
( @RPTNAM varchar(15),@NROITM INTEGER,@PANAM varchar(50), @PVALUE text)

/*
APLICACION: ACTUALIZACION VALORES EN JOB DE REPORTES
DESARROLLADO POR: CARLOS L. PEDRANTI
ULTIMA MODIFICACION: 27/06/2008

Usr_Sp_JOBREI_Insert 'USR_PPPRUEBA',1,'p_parametro','valor'

*/
AS
SET NOCOUNT ON

DECLARE @error_stat int,
        @return_stat int,
        @row_count int,
        @msgerror varchar(255)

BEGIN TRAN
	INSERT INTO USR_JOBREI
	( USR_JOBREI_RPTNAM,
	USR_JOBREI_NROITM,
	USR_JOBREI_PANAM,
	USR_JOBREI_PVALUE,
	USR_JO_FECALT,
	USR_JO_FECMOD,
	USR_JO_USERID,
	USR_JO_ULTOPR,
	USR_JO_DEBAJA,
	USR_JO_OALIAS)

	SELECT
	@RPTNAM USR_JOBREI_RPTNAM,
	@NROITM USR_JOBREI_NROITM,
	@PANAM USR_JOBREI_PANAM,
	@PVALUE USR_JOBREI_PVALUE,
	GETDATE() USR_JO_FECALT,
	GETDATE() USR_JO_FECMOD,
	'PROCESO' USR_JO_USERID,
	'A' USR_JO_ULTOPR,
	'N' USR_JO_DEBAJA,
	'USR_JOBREI' USR_JO_OALIAS

	SELECT  @error_stat = @@ERROR,
        @row_count  = @@ROWCOUNT       

	IF @error_stat <> 0
	BEGIN
		ROLLBACK TRAN
		Select @msgerror=
		'@?@2#USR_JOBREI#' +
		'#****Error Insertando en USR_JOBREI**#'
		RAISERROR(@msgerror, 16, 1)
		RETURN  @error_stat
	END

IF @row_count <> 1
BEGIN
   ROLLBACK TRAN
   Select @msgerror=
   '@?@2#USR_JOBREH#' +
   '#******Cant de valores actualizados <> 1 en Usr_Sp_JOBREI_Insert*****#'
   RAISERROR(@msgerror, 16, 1)
   RETURN -999
END
COMMIT TRAN
RETURN 0