Imports BD.ABD
Imports System.Data.SqlClient

Public Class Auditoria
    Private funcion As New funcion.Funcion
    Private ser As BD.ABD
    Private sqlc As SqlConnection
    Private cmd As SqlCommand
    Private myTrans As SqlTransaction
    Private sql As String
    Private dtCodigo As DataTable
    Private dtDetalle As DataTable
    Private dtMonitoreo As DataTable

    Private dr As SqlDataReader
    Private da As SqlDataAdapter

    Private usuario As String
    Private nivel As String

    Private dtSistema As DataTable
    Private dtModulo As DataTable
    Private dtModuloUsuario As DataTable

    Public Sub New()
        ser = New BD.ABD

        dtCodigo = New DataTable("CODIGO")
        dtDetalle = New DataTable("AUDITORIA")
        dtMonitoreo = New DataTable("MONITOREO")
        dtSistema = New DataTable("SISTEMA")
        dtModulo = New DataTable("MODULO")
        dtModuloUsuario = New DataTable("MODULOUSUARIO")

        cmd = New SqlCommand

        sqlc = New SqlConnection
    End Sub

    Public Sub InsertarTabla(ByVal tabla As String, _
    ByVal codigo As String, ByVal descripcion As String, _
    ByVal insertar As Byte, ByVal modificar As Byte, ByVal eliminar As Byte, _
    ByVal codUsuario As Long, ByVal codEmpresa As Long, _
    ByVal cmd As SqlCommand)
        'Para traer el usuario
        usuario = funcion.Descripcion_Tabla("DESUSUARIO", "USUARIO", "CODUSUARIO", codUsuario, codEmpresa)
        Select Case funcion.Descripcion_Tabla("NIVELCUENTA", "USUARIO", "CODUSUARIO", codUsuario, codEmpresa)
            Case Is = 1
                nivel = "Administrador"
            Case Is = 2
                nivel = "Supervisor"
            Case Else
                nivel = "Operador"
        End Select

        sql = ""
        sql = "DECLARE @CODIGO AS NUMERIC(25) " & _
        "SET @CODIGO = (SELECT ISNULL(MAX(CODIGO), 0) + 1 FROM AUDITORIATABLAS) " & _
        "INSERT INTO AUDITORIATABLAS(CODIGO, EMPRESA, TABLA, NUMCODIGO, DESCRIPCION, " & _
        "USUARIO, NIVEL, INSERTAR, MODIFICAR, ELIMINAR) " & _
        "VALUES(@CODIGO, " & codEmpresa & ", '" & tabla & "', '" & codigo & "', '" & descripcion & "', " & _
        "'" & usuario & "', '" & nivel & "', " & insertar & ", " & modificar & ", " & eliminar & ")"

        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
    End Sub

    Public Sub InsertarMovimiento(ByVal movimiento As String, _
    ByVal sucursal As String, ByVal comprobante As String, ByVal numero As String, _
    ByVal moneda As String, ByVal cotizacion1 As String, ByVal cotizacion2 As String, _
    ByVal fechaMovimiento As String, ByVal fechaProceso As String, _
    ByVal observacion As String, ByVal importe As String, ByVal detalle As String, _
    ByVal insertar As Byte, ByVal modificar As Byte, ByVal eliminar As Byte, _
    ByVal codUsuario As Long, ByVal codEmpresa As Long, _
    ByVal cmd As SqlCommand)
        'Para traer el usuario
        usuario = funcion.Descripcion_Tabla("DESUSUARIO", "USUARIO", "CODUSUARIO", codUsuario, codEmpresa)
        Select Case funcion.Descripcion_Tabla("NIVELCUENTA", "USUARIO", "CODUSUARIO", codUsuario, codEmpresa)
            Case Is = 1
                nivel = "Administrador"
            Case Is = 2
                nivel = "Supervisor"
            Case Else
                nivel = "Operador"
        End Select

        sql = ""
        sql = "DECLARE @CODIGO AS NUMERIC(25) " & _
        "SET @CODIGO = (SELECT ISNULL(MAX(CODIGO), 0) + 1 FROM AUDITORIAMOVIMIENTOS) " & _
        "INSERT INTO AUDITORIAMOVIMIENTOS(CODIGO, EMPRESA, MOVIMIENTO, SUCURSAL, " & _
        "COMPROBANTE, NUMERO, MONEDA, COTIZACION1, COTIZACION2, FECHAMOVIMIENTO, FECHAPROCESO, " & _
        "OBSERVACION, IMPORTE, DETALLE, USUARIO, NIVEL, INSERTAR, MODIFICAR, ELIMINAR) " & _
        "VALUES(@CODIGO, " & codEmpresa & ", '" & movimiento & "', '" & sucursal & "', " & _
        "'" & comprobante & "', '" & numero & "', '" & moneda & "', '" & cotizacion1 & "', '" & cotizacion2 & "', " & _
        "'" & fechaMovimiento & "', '" & fechaProceso & "', '" & observacion & "', " & _
        "'" & importe & "', '" & detalle & "', " & _
        "'" & usuario & "', '" & nivel & "', " & insertar & ", " & modificar & ", " & eliminar & ")"

        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
    End Sub

    Public Sub Eliminar(ByVal codigo As Long, _
    ByVal cmd As SqlCommand)
        Try
            sql = ""
            sql = "DELETE FROM CARGO WHERE CODCARGO = " & codigo & " "

            cmd.CommandText = sql
            cmd.ExecuteNonQuery()
        Catch e As Exception

        Finally

        End Try
    End Sub

    Public Function Tablas() As DataSet
        Dim ds As New DataSet

        ser.Abrir(sqlc)

        da = New SqlDataAdapter

        sql = ""
        sql = "SELECT DISTINCT TABLA AS ARCHIVO, TABLA AS VALOR FROM AUDITORIATABLAS"

        Try
            cmd.Connection = sqlc
            cmd.CommandText = sql

            da = New SqlDataAdapter(cmd)
            da.Fill(ds)
        Catch ex As Exception

        Finally
            ser.Cerrar(sqlc)
        End Try

        Return ds
    End Function

    Public Function Movimientos() As DataSet
        Dim ds As New DataSet

        ser.Abrir(sqlc)

        da = New SqlDataAdapter

        sql = ""
        sql = "SELECT DISTINCT MOVIMIENTO AS ARCHIVO, MOVIMIENTO AS VALOR FROM AUDITORIAMOVIMIENTOS"

        Try
            cmd.Connection = sqlc
            cmd.CommandText = sql

            da = New SqlDataAdapter(cmd)
            da.Fill(ds)
        Catch ex As Exception

        Finally
            ser.Cerrar(sqlc)
        End Try

        Return ds
    End Function

    Public Function EliminarHistorialTablas(ByVal condicion As String, ByVal codEmpresa As Long) As Boolean
        ser.Abrir(sqlc)

        myTrans = sqlc.BeginTransaction(IsolationLevel.ReadCommitted, "EliminarHistoTablas")
        cmd.Connection = sqlc
        cmd.Transaction = myTrans

        Try
            sql = ""
            sql = "DELETE FROM AUDITORIATABLAS  WHERE EMPRESA = " & codEmpresa & " " & condicion & ""

            cmd.CommandText = sql
            cmd.ExecuteNonQuery()

            myTrans.Commit()

            Return True
        Catch e As Exception
            Try
                myTrans.Rollback("EliminarHistoTablas")
            Catch
                If Not myTrans.Connection Is Nothing Then
                    Return False
                End If
            End Try

            Return False
        Finally
            ser.Cerrar(sqlc)
        End Try
    End Function

    Public Function EliminarHistorialMovimientos(ByVal condicion As String, ByVal codEmpresa As Long) As Boolean
        ser.Abrir(sqlc)

        myTrans = sqlc.BeginTransaction(IsolationLevel.ReadCommitted, "EliminarHistoMovimientos")
        cmd.Connection = sqlc
        cmd.Transaction = myTrans

        Try
            sql = ""
            sql = "DELETE FROM AUDITORIAMOVIMIENTOS  WHERE EMPRESA = " & codEmpresa & " " & condicion & ""

            cmd.CommandText = sql
            cmd.ExecuteNonQuery()

            myTrans.Commit()

            Return True
        Catch e As Exception
            Try
                myTrans.Rollback("EliminarHistoMovimientos")
            Catch
                If Not myTrans.Connection Is Nothing Then
                    Return False
                End If
            End Try

            Return False
        Finally
            ser.Cerrar(sqlc)
        End Try
    End Function

    Public Function DsRecorrerCodigoTablas(ByVal codigo As Long) As DataTable
        dtCodigo.Rows.Clear()

        ser.Abrir(sqlc)

        sql = ""
        sql = "SELECT DISTINCT CONVERT(VARCHAR(10),FECHA,103) AS FECHA " & _
        "FROM AUDITORIATABLAS WHERE EMPRESA = " & codigo & " "

        Try
            cmd.Connection = sqlc
            cmd.CommandText = sql

            da = New SqlDataAdapter(cmd)
            da.Fill(dtCodigo)
        Catch ex As Exception

        Finally
            ser.Cerrar(sqlc)
        End Try

        Return dtCodigo
    End Function

    Public Function DsRecorrerDescripcionTablas(ByVal fecha As String, ByVal codEmpresa As Long) As DataTable
        dtDetalle.Rows.Clear()

        ser.Abrir(sqlc)

        sql = ""

        sql = " SELECT A.CODIGO AS NUMEROABM, E.NOMFANTASIA AS EMPRESA," & _
              " CONVERT(VARCHAR(10), A.FECHA, 108) AS HORAABM, A.TABLA AS ARCHIVO, " & _
              " A.NUMCODIGO AS CODIGO, A.DESCRIPCION AS DESCRIPCION, " & _
              " A.USUARIO AS USUARIO, A.NIVEL AS NIVEL, " & _
              " CASE " & _
              " WHEN A.INSERTAR = 1 THEN 'Registro creado' " & _
              " WHEN A.MODIFICAR = 1 THEN 'Registro modificado' " & _
              " WHEN A.ELIMINAR = 1 THEN 'Registro eliminado' " & _
              " END AS ABM, " & _
              " CONVERT(VARCHAR(10), A.FECHA, 103) AS FECHAABM " & _
              " FROM AUDITORIATABLAS A INNER JOIN EMPRESA E ON A.EMPRESA = E.CODEMPRESA " & _
              " WHERE CODEMPRESA = " & codEmpresa & " " & _
              " AND FECHA >= CAST('" & fecha & " 00:00:00 ' AS DATETIME) AND FECHA <= CAST('" & fecha & " 23:59:59 ' AS DATETIME) " & _
              " ORDER BY HORAABM, ARCHIVO "

        Try
            cmd.Connection = sqlc
            cmd.CommandText = sql

            da = New SqlDataAdapter(cmd)
            da.Fill(dtDetalle)
        Catch ex As Exception

        Finally
            ser.Cerrar(sqlc)
        End Try

        Return dtDetalle
    End Function

    Public Function DsRecorrerCodigoMovimientos(ByVal codigo As Long) As DataTable
        dtCodigo.Rows.Clear()

        ser.Abrir(sqlc)

        sql = ""
        sql = "SELECT DISTINCT CONVERT(VARCHAR(10),FECHA,103) AS FECHA " & _
        "FROM AUDITORIAMOVIMIENTOS WHERE EMPRESA = " & codigo & " "

        Try
            cmd.Connection = sqlc
            cmd.CommandText = sql

            da = New SqlDataAdapter(cmd)
            da.Fill(dtCodigo)
        Catch ex As Exception

        Finally
            ser.Cerrar(sqlc)
        End Try

        Return dtCodigo
    End Function

    Public Function DsRecorrerDescripcionMovimientos(ByVal fecha As String, ByVal codEmpresa As Long) As DataTable
        dtDetalle.Rows.Clear()

        ser.Abrir(sqlc)

        sql = ""

        sql = " SELECT A.CODIGO AS NUMEROABM, E.NOMFANTASIA AS EMPRESA, A.MOVIMIENTO AS MOVIMIENTO, " & _
              " A.SUCURSAL AS SUCURSAL, A.COMPROBANTE AS COMPROBANTE,  A.IMPORTE, " & _
              " A.NUMERO AS NUMERO, A.MONEDA AS MONEDA, A.COTIZACION1, A.COTIZACION2, " & _
              " A.FECHAMOVIMIENTO, A.FECHAPROCESO,A.DETALLE AS LINEASDETALLE, " & _
              " A.USUARIO, A.NIVEL, " & _
              " CASE " & _
              " WHEN A.INSERTAR = 1 THEN 'Registro creado' " & _
              " WHEN A.MODIFICAR = 1 THEN 'Registro modificado' " & _
              " WHEN A.ELIMINAR = 1 THEN 'Registro eliminado' " & _
              " END AS ABM, " & _
              " CONVERT(VARCHAR(10), A.FECHA, 103) AS FECHAABM, " & _
              " CONVERT(VARCHAR(10), A.FECHA, 108) AS HORAABM " & _
              " FROM AUDITORIAMOVIMIENTOS A INNER JOIN EMPRESA E ON A.EMPRESA = E.CODEMPRESA " & _
              " WHERE CODEMPRESA =   " & codEmpresa & " " & _
              " AND FECHA >= CAST('" & fecha & " 00:00:00 ' AS DATETIME) AND FECHA <= CAST('" & fecha & " 23:59:59 ' AS DATETIME) " & _
              " ORDER BY HORAABM, A.MOVIMIENTO  "

        Try
            cmd.Connection = sqlc
            cmd.CommandText = sql

            da = New SqlDataAdapter(cmd)
            da.Fill(dtDetalle)
        Catch ex As Exception

        Finally
            ser.Cerrar(sqlc)
        End Try

        Return dtDetalle
    End Function

    Public Function Monitoreo(ByVal codEmpresa As Long, ByVal fecha As String, _
    ByRef cnn As SqlConnection) As DataTable
        dtMonitoreo.Rows.Clear()

        If cnn.State = ConnectionState.Closed Then
            ser.Abrir(cnn)
        End If

        sql = ""
        sql = " SELECT FECHA, CONVERT(VARCHAR(10), A.FECHA, 108) AS HORAABM, A.MOVIMIENTO AS MOVIMIENTO, " & _
              " A.USUARIO, A.NIVEL, " & _
              " CASE " & _
              " WHEN A.INSERTAR = 1 THEN 'Registro creado' " & _
              " WHEN A.MODIFICAR = 1 THEN 'Registro modificado' " & _
              " WHEN A.ELIMINAR = 1 THEN 'Registro eliminado' " & _
              " END AS ABM " & _
              " FROM AUDITORIAMOVIMIENTOS A " & _
              " WHERE EMPRESA =   " & codEmpresa & " " & _
              " AND FECHA >= CAST('" & fecha & " 00:00:00 ' AS DATETIME) AND FECHA <= CAST('" & fecha & " 23:59:59 ' AS DATETIME) " & _
              " UNION ALL " & _
              " SELECT FECHA, CONVERT(VARCHAR(10), A.FECHA, 108) AS HORAABM, A.TABLA AS MOVIMIENTO, " & _
              " A.USUARIO, A.NIVEL, " & _
              " CASE " & _
              " WHEN A.INSERTAR = 1 THEN 'Registro creado' " & _
              " WHEN A.MODIFICAR = 1 THEN 'Registro modificado' " & _
              " WHEN A.ELIMINAR = 1 THEN 'Registro eliminado' " & _
              " END AS ABM " & _
              " FROM AUDITORIATABLAS A " & _
              " WHERE EMPRESA =   " & codEmpresa & " " & _
              " AND FECHA >= CAST('" & fecha & " 00:00:00 ' AS DATETIME) AND FECHA <= CAST('" & fecha & " 23:59:59 ' AS DATETIME) " & _
              " ORDER BY FECHA, MOVIMIENTO  "

        Try
            cmd.Connection = cnn
            cmd.CommandText = sql

            da = New SqlDataAdapter(cmd)
            da.Fill(dtMonitoreo)
        Catch ex As Exception

        Finally

        End Try

        Return dtMonitoreo
    End Function

    Public Function DsSistemas(ByVal empcodigo As Long) As DataTable
        dtSistema.Rows.Clear()

        ser.Abrir(sqlc)

        sql = " SELECT CODSISTEMAS AS CODIGO, RTRIM(NUMSISTEMAS)  AS NUMSISTEMA, " & _
              " RTRIM(DESSISTEMAS) AS DESCRIPCION FROM SISTEMAS " & _
              " WHERE CODEMPRESA = " & empcodigo & " " & _
              " ORDER BY DESCRIPCION"

        Try
            cmd.Connection = sqlc
            cmd.CommandText = sql

            da = New SqlDataAdapter(cmd)
            da.Fill(dtSistema)
        Catch ex As Exception

        Finally
            ser.Cerrar(sqlc)
        End Try

        Return dtSistema
    End Function

    Public Function DsModulos(ByVal codSistema As Long, ByVal empcodigo As Long) As DataTable
        dtModulo.Rows.Clear()

        ser.Abrir(sqlc)

        sql = " SELECT CODMODULO AS CODIGO, RTRIM(DESMODULO) AS DESCRIPCION " & _
              " FROM MODULOS M, SISTEMAS S WHERE M.CODSISTEMAS = S.CODSISTEMAS " & _
              " AND M.CODSISTEMAS = " & codSistema & " AND M.CODEMPRESA = " & empcodigo & " " & _
              " ORDER BY DESCRIPCION "

        Try
            cmd.Connection = sqlc
            cmd.CommandText = sql

            da = New SqlDataAdapter(cmd)
            da.Fill(dtModulo)
        Catch ex As Exception

        Finally
            ser.Cerrar(sqlc)
        End Try

        Return dtModulo
    End Function

    Public Function DsRecorrerModuloUsuario(ByVal CodUsuario As Long, ByVal CodSistema As Long, ByVal codEmpresa As Long) As DataTable
        dtModuloUsuario.Rows.Clear()

        ser.Abrir(sqlc)

        sql = ""

        sql = " SELECT MU.CODMODULO AS CODMODULO, " & _
              " CASE  WHEN  MU.[SELECT] = 0 THEN 'No' ELSE 'Si' END AS SELECTS, " & _
              " CASE WHEN MU.[INSERT] = 0 THEN 'No' ELSE 'Si'  END AS INSERTS, " & _
              " CASE WHEN  MU.[UPDATE] = 0 THEN 'No' ELSE 'Si' END AS UPDATES, " & _
              " CASE WHEN MU.[DELETE] = 0 THEN 'No' ELSE 'Si' END  AS DELETES, " & _
              " CASE WHEN MU.[PRINT] = 0 THEN 'No' ELSE 'Si'  END AS PRINTS, " & _
              " CASE WHEN MU.[LIMITECREDITO] = 0 THEN 'No' ELSE 'Si'END AS LIMITECREDITO, " & _
              " CASE WHEN MU.[DESC] = 0 THEN 'No'ELSE 'Si' END AS DESCUENTOS, " & _
              " CASE WHEN MU.VENCIDAS = 0 THEN 'No' ELSE 'Si' END AS VENCIDAS, " & _
              " CASE WHEN  MU.ANULAR = 0 THEN 'No' ELSE 'Si' END AS ANULAR, " & _
              " M.DESMODULO AS DESMODULO " & _
              " FROM MODULOS M, MODULOUSUARIO MU " & _
              " WHERE M.CODSISTEMAS =        " & CodSistema & "  AND " & _
              " MU.CODMODULO = M.CODMODULO AND " & _
              " MU.CODUSUARIO =   " & CodUsuario & " AND " & _
              " MU.CODEMPRESA =   " & codEmpresa & " " & _
              " ORDER BY DESMODULO "

        Try
            cmd.Connection = sqlc
            cmd.CommandText = sql

            da = New SqlDataAdapter(cmd)
            da.Fill(dtModuloUsuario)
        Catch ex As Exception

        Finally
            ser.Cerrar(sqlc)
        End Try

        Return dtModuloUsuario
    End Function
End Class