Imports System.Data.SqlClient
Public Class sentTecnicos
    Private funcion As New Funcion.Funcion
    Private auditoria As New SentAuditoria.Auditoria
    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 codigo As String, ByVal descripcion As String, _
     ByVal producto1 As String, ByVal codUsuario As Long, ByVal codEmpresa As Long) As Boolean
        ser.Abrir(sqlc)

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

        Try
            sql = ""
            sql = "DECLARE @CODIGO AS NUMERIC(18) " & _
            "SET @CODIGO = (SELECT ISNULL(MAX(CODTECNICO), 0) + 1 FROM TECNICOS) " & _
            "INSERT INTO TECNICOS(CODTECNICO, NUMTECNICO, DESTECNICO, " & _
            "CODUSUARIO, CODEMPRESA, FECGRA) " & _
            "VALUES(@CODIGO, '" & codigo & "', '" & descripcion & "', " & codUsuario & ", " & _
            "" & codEmpresa & ", GETDATE())"

            cmd.CommandText = sql
            cmd.ExecuteNonQuery()

            'Para la auditoria de tablas
            auditoria.InsertarTabla("" & producto1 & "", codigo, descripcion, 1, 0, 0, codUsuario, codEmpresa, cmd)

            myTrans.Commit()

            Return True
        Catch e As Exception
            Try
                myTrans.Rollback("InsertarTecnico")
            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 Modificar(ByVal codigo As Long, ByVal numcodigo As String, ByVal descripcion As String, _
     ByVal producto1 As String, ByVal codUsuario As Long, ByVal codEmpresa As Long) As Boolean
        ser.Abrir(sqlc)

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

        Try
            sql = ""
            sql = "UPDATE TECNICOS " & _
            "SET DESTECNICO = '" & descripcion & "', CODUSUARIO = " & codUsuario & ", " & _
            "FECGRA = GETDATE() " & _
            "WHERE CODTECNICO = " & codigo & ""

            cmd.CommandText = sql
            cmd.ExecuteNonQuery()

            'Para la auditoria de tablas
            auditoria.InsertarTabla("" & producto1 & "", numcodigo, descripcion, 0, 1, 0, codUsuario, codEmpresa, cmd)

            myTrans.Commit()

            Return True
        Catch e As Exception
            Try
                myTrans.Rollback("ModificarTecnicos")
            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 codigo As Long, ByVal numcodigo As String, ByVal descripcion As String, ByVal producto1 As String, ByVal codUsuario As Long, ByVal codEmpresa As Long) As Boolean
        ser.Abrir(sqlc)

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

        Try
            sql = ""
            sql = "DELETE FROM  TECNICOS WHERE CODTECNICO = " & codigo & " "

            cmd.CommandText = sql
            cmd.ExecuteNonQuery()

            'Para la auditoria de tablas
            auditoria.InsertarTabla("" & producto1 & "", numcodigo, descripcion, 0, 0, 1, codUsuario, codEmpresa, cmd)

            myTrans.Commit()

            Return True
        Catch e As Exception
            Try
                myTrans.Rollback("EliminarTecnicos")
            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 CODTECNICO FROM TECNICOS 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 codigo As Long) As DataTable
        dtDescripcion.Rows.Clear()

        ser.Abrir(sqlc)

        sql = ""
        sql = "SELECT RTRIM(NUMTECNICO) AS NUMTECNICO, RTRIM(DESTECNICO) AS DESTECNICO FROM  TECNICOS " & _
        "WHERE CODTECNICO = " & codigo & ""

        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
End Class
