Imports System.Data.SqlClient

Public Class sentUsuarios
    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 dtDescripcion As DataTable
    Private dr As SqlDataReader
    Private da As SqlDataAdapter

    Public Sub New()
        ser = New BD.ABD

        dtCodigo = New DataTable("CODIGO")
        dtDescripcion = New DataTable("DESCRIPCION")
       
        cmd = New SqlCommand

        sqlc = New SqlConnection
    End Sub

    Public Function Insertar(ByVal DescripcionUsuario As String, ByVal Contraseņa As String, ByVal Nivel As Integer, ByVal UsuarioGra As String, ByVal CodEmpresa As Long) As Boolean

        ser.Abrir(sqlc)

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

        Try
            sql = ""

            sql = "DECLARE @CODIGO AS NUMERIC(18) " & _
                  "SET @CODIGO = (SELECT ISNULL(MAX(CODUSUARIO), 0) + 1 FROM USUARIO) " & _
                  "INSERT INTO USUARIO(CODUSUARIO, DESUSUARIO, PASSUSUARIO, NIVELCUENTA, USUGRA,  CODEMPRESA, FECGRA) " & _
                  " VALUES (@CODIGO, '" & DescripcionUsuario & "', '" & Contraseņa & "', " & Nivel & ", " & UsuarioGra & ", " & CodEmpresa & ", GETDATE())"

            cmd.CommandText = sql
            cmd.ExecuteNonQuery()

            myTrans.Commit()

            Return True

        Catch e As Exception
            Try
                myTrans.Rollback("InsertarUsuario")
            Catch ex As SqlException
                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 Modificar(ByVal CodigoUsuario As Long, ByVal DescripcionUsuario As String, ByVal Contrasena As String, ByVal Nivel As Integer, ByVal UsuarioGra As Long, ByVal CodEmpresa As Long) As Boolean
        ser.Abrir(sqlc)


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

        Try
            sql = ""

            sql = "UPDATE USUARIO SET DESUSUARIO = '" & DescripcionUsuario & "', " & _
                  " PASSUSUARIO = '" & Contrasena & "', NIVELCUENTA = " & Nivel & ", " & _
                  " USUGRA = " & UsuarioGra & ", FECGRA = GETDATE() " & _
                  " WHERE CODEMPRESA  = " & CodEmpresa & " AND CODUSUARIO = " & CodigoUsuario & " "

            cmd.CommandText = sql
            cmd.ExecuteNonQuery()

            myTrans.Commit()

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

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

    Public Function Eliminar(ByVal codigoUsuario As Long, ByVal codEmpresa As Long) As Boolean
        ser.Abrir(sqlc)

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

        Try
            sql = ""
            sql = "DELETE FROM USUARIO WHERE CODEMPRESA = " & codEmpresa & " AND CODUSUARIO = " & codigoUsuario & ""

            cmd.CommandText = sql
            cmd.ExecuteNonQuery()

            myTrans.Commit()

            Return True
        Catch e As Exception
            Try
                myTrans.Rollback("EliminarUsuario")
            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 DsRecorrerCodigo(ByVal codigo As Long) As DataTable
        dtCodigo.Rows.Clear()

        ser.Abrir(sqlc)

        sql = ""
        sql = "SELECT CODUSUARIO FROM USUARIO WHERE CODEMPRESA = " & 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 DsRecorrerDescripcion(ByVal codigoUsuario As Long) As DataTable
        dtDescripcion.Rows.Clear()

        ser.Abrir(sqlc)

        sql = ""

        sql = "SELECT RTRIM(U.DESUSUARIO) AS DESUSUARIO, " & _
              "RTRIM(U.PASSUSUARIO) AS PASSUSUARIO, RTRIM(U.NIVELCUENTA)AS NIVELCUENTA " & _
              "FROM USUARIO U WHERE U.CODUSUARIO = " & codigoUsuario & " " & _
              "ORDER BY DESUSUARIO"

        Try
            cmd.Connection = sqlc
            cmd.CommandText = sql

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

        Finally
            ser.Cerrar(sqlc)
        End Try

        Return dtDescripcion
    End Function

    Public Function InsertarModulos(ByVal codEmpresa As Long, ByVal codUsuario As Long, _
    ByVal sistema As Long, ByVal modulo As String) As Boolean
        ser.Abrir(sqlc)

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

        Try
            sql = ""
            sql = "DECLARE @CODIGO AS NUMERIC(18) " & _
            "SET @CODIGO = (SELECT ISNULL(MAX(CODMODULO), 0) + 1 FROM MODULOS) " & _
            "IF NOT EXISTS(SELECT DESMODULO FROM MODULOS WHERE CODSISTEMAS = " & sistema & " " & _
            "AND DESMODULO = '" & modulo & "' AND CODEMPRESA = " & codEmpresa & ") " & vbCrLf & _
            "BEGIN " & vbCrLf & _
            "   INSERT INTO MODULOS(CODMODULO, CODSISTEMAS, CODEMPRESA, DESMODULO, USUARIO, FECGRA) " & _
            "   VALUES (@CODIGO, " & sistema & ", " & codEmpresa & ", '" & modulo & "', " & _
            "   " & codUsuario & ", GETDATE()) " & _
            "END"

            cmd.CommandText = sql
            cmd.ExecuteNonQuery()

            myTrans.Commit()

            Return True

        Catch e As Exception
            Try
                myTrans.Rollback("InsertarModulo")
            Catch ex As SqlException
                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 InsertarSistemas(ByVal codEmpresa As Long, ByVal codUsuario As Long, _
    ByVal numSistema As String, ByVal desSistema As String) As Boolean
        ser.Abrir(sqlc)

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

        Try
            sql = ""
            sql = "DECLARE @CODIGO AS NUMERIC(18) " & _
            "SET @CODIGO = (SELECT ISNULL(MAX(CODSISTEMAS), 0) + 1 FROM SISTEMAS) " & _
            "IF NOT EXISTS(SELECT DESSISTEMAS FROM SISTEMAS WHERE NUMSISTEMAS = '" & numSistema & "' " & _
            "AND CODEMPRESA = " & codEmpresa & ") " & vbCrLf & _
            "BEGIN " & vbCrLf & _
            "   INSERT INTO SISTEMAS(CODSISTEMAS, NUMSISTEMAS, CODEMPRESA, DESSISTEMAS, CODUSUARIO, FECGRA) " & _
            "   VALUES (@CODIGO, " & numSistema & ", " & codEmpresa & ", '" & desSistema & "', " & _
            "   " & codUsuario & ", GETDATE()) " & _
            "END"

            cmd.CommandText = sql
            cmd.ExecuteNonQuery()

            myTrans.Commit()

            Return True

        Catch e As Exception
            Try
                myTrans.Rollback("InsertarSistema")
            Catch ex As SqlException
                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 ExisteModuloUsuario(ByVal CodSistema As Long, ByVal CodUsuario As Long, ByVal CodModulo As Long, ByVal CodEmpresa As Long) As Long
        ser.Abrir(sqlc)

        sql = ""
        sql = "SELECT MAX(CODMODULO) as CODIGO FROM MODULOUSUARIO WHERE CODMODULO = " & CodModulo & " AND CODUSUARIO = " & CodUsuario & " AND CODEMPRESA = " & CodEmpresa & " "

        Try
            cmd.CommandText = sql
            cmd.Connection = sqlc

            dr = cmd.ExecuteReader()
            Try
                dr.Read()
                Return dr("CODIGO")
            Catch
                Return 0
            Finally
                dr.Close()
                ser.Cerrar(sqlc)
            End Try
        Finally
        End Try
    End Function

    Public Function ModificarModuloUsuario(ByVal CodModulo As Long, ByVal CodUsuario As Long, ByVal ModSelect As Integer, ByVal ModInsert As Integer, ByVal ModUpdate As Integer, ByVal ModDelete As Integer, ByVal ModPrint As Integer, ByVal ModVencidas As Integer, ByVal ModLimiteCredito As Integer, ByVal ModDescuentos As Integer, ByVal ModAnular As Integer, ByVal CodEmpresa As Long) As Boolean
        ser.Abrir(sqlc)


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

        Try
            sql = ""

            sql = "UPDATE MODULOUSUARIO SET [SELECT] = " & ModSelect & ", " & _
                  " [INSERT] = " & ModInsert & ", [UPDATE] = " & ModUpdate & ", " & _
                  " [DELETE] = " & ModDelete & ", [PRINT] = " & ModPrint & ", " & _
                  " VENCIDAS = " & ModVencidas & ", LIMITECREDITO = " & ModLimiteCredito & ",  " & _
                  " [DESC] = " & ModDescuentos & ", ANULAR = " & ModAnular & " WHERE CODMODULO = " & CodModulo & " AND CODUSUARIO = " & CodUsuario & ""

            cmd.CommandText = sql
            cmd.ExecuteNonQuery()

            myTrans.Commit()

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

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

    Public Function InsertarModuloUsuario(ByVal CodModulo As Long, ByVal CodUsuario As Long, ByVal ModSelect As Integer, ByVal ModInsert As Integer, ByVal ModUpdate As Integer, ByVal ModDelete As Integer, ByVal ModPrint As Integer, ByVal ModVencidas As Integer, ByVal ModLimiteCredito As Integer, ByVal ModDescuentos As Integer, ByVal ModAnular As Integer, ByVal CodEmpresa As Long) As Boolean

        ser.Abrir(sqlc)

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

        Try
            sql = ""
            sql = "INSERT INTO MODULOUSUARIO(CODUSUARIO, CODEMPRESA, CODMODULO, " & _
           "[SELECT], [INSERT], [UPDATE], [DELETE], [PRINT], VENCIDAS, LIMITECREDITO, " & _
           "[DESC], ANULAR, FECGRA) " & _
           "VALUES (" & CodUsuario & ", " & CodEmpresa & ", " & CodModulo & ", " & _
           "" & ModSelect & ", " & ModInsert & ", " & ModUpdate & ", " & ModDelete & ", " & _
           "" & ModPrint & ", " & ModVencidas & ", " & ModLimiteCredito & ", " & ModDescuentos & ", " & _
           "" & ModAnular & ",GETDATE()) "

            cmd.CommandText = sql
            cmd.ExecuteNonQuery()

            myTrans.Commit()

            Return True

        Catch e As Exception
            Try
                myTrans.Rollback("InsertarModuloUsuario")
            Catch ex As SqlException
                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 EliminarModuloUsuario(ByVal codigoUsuario As Long, ByVal codModulo As Long, ByVal codEmpresa As Long) As Boolean
        ser.Abrir(sqlc)

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

        Try
            sql = ""
            sql = "DELETE FROM MODULOUSUARIO WHERE CODMODULO = " & codModulo & " AND " & _
                  " CODUSUARIO = " & codigoUsuario & " AND CODEMPRESA = " & codEmpresa & " "

            cmd.CommandText = sql
            cmd.ExecuteNonQuery()

            myTrans.Commit()

            Return True
        Catch e As Exception
            Try
                myTrans.Rollback("EliminarModuloUsuario")
            Catch

                If Not myTrans.Connection Is Nothing Then
                    Return False
                End If
            End Try
            MsgBox(e.ToString)
            Return False
        Finally
            ser.Cerrar(sqlc)
        End Try
    End Function

    Public Function ModificarModuloPermiso(ByVal CodModulo As Long, ByVal CodUsuario As Long, ByVal columna As String, ByVal permiso As Integer, ByVal CodEmpresa As Long) As Boolean
        ser.Abrir(sqlc)


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

        Try
            sql = ""

            sql = "UPDATE MODULOUSUARIO SET " & columna & " = " & permiso & " " & _
                 " WHERE CODMODULO = " & CodModulo & " AND CODUSUARIO = " & CodUsuario & "" & _
                 " AND CODEMPRESA = " & CodEmpresa & " "

            cmd.CommandText = sql
            cmd.ExecuteNonQuery()

            myTrans.Commit()

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

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