Imports System.Data.SqlClient
Imports BD.ABD
Imports Funcion.Funcion
Public Class Empresa
    Dim ser As New BD.ABD
    Dim sentFuncion As New Funcion.Funcion
    Dim sql As String
    Dim cmd As SqlCommand
    Dim ds As DataSet
    Dim dr As SqlDataReader
    Dim da As SqlDataAdapter
    Dim cnn As SqlConnection

    Dim strFecha As String
    Dim formato As New System.Globalization.CultureInfo("es-PY", True)
    Dim fecha, b As System.DateTime
    Public Function Insertar(ByVal codEmpresa As Integer, ByVal usugra As String, ByVal nomfantasia As String, ByVal nomcontribuyente As String, ByVal ruccontribuyente As String, ByVal nomrepresentante As String, ByVal rucrepresentante As String, ByVal desempresa As String, ByVal direccion As String, ByVal telefono As String, ByVal email As String, ByVal numregpatronal As String, ByVal retentor As Integer) As Integer
        cnn = New SqlConnection
        cnn = ser.Abrir()
        codEmpresa = sentFuncion.MaximoMas1("CODEMPRESA", "EMPRESA", cnn)
        Try
            'para actualizar la fecha
            b = System.DateTime.Parse(Now, formato)
            strFecha = b

            sql = ""
            sql = "INSERT INTO Empresa(CODEMPRESA, USUGRA, NOMFANTASIA, NOMCONTRIBUYENTE, RUCCONTRIBUYENTE, NOMREPRESENTANTE, RUCREPRESENTANTE,  " & _
            "DESEMPRESA, DIRECCION, TELEFONO, EMAIL, NUMREGPATRONAL, RETENTOR, FECGRA) " & _
            " VALUES (" & codEmpresa & ", " & usugra & ", '" & nomfantasia & "',  " & _
            "'" & nomcontribuyente & " ', '" & ruccontribuyente & " ', '" & nomrepresentante & " ', '" & rucrepresentante & " ', '" & desempresa & "','" & direccion & "','" & telefono & "', " & _
            "'" & email & "','" & numregpatronal & "','" & retentor & "','" & strFecha & "' )" 'sintaxis sql
            cmd = New SqlCommand(sql, cnn)                    'crea nuevo comando SQL con sql y la conexxion1
            cmd.ExecuteNonQuery()                               'ejecuta el comando
            Return codEmpresa
        Catch
            Return 0
        Finally
            cnn.Close()
        End Try
        Return codEmpresa
    End Function
    Public Function Eliminar(ByVal valor_de_la_clave_primaria As String) As Boolean
        cnn = ser.Abrir()
        sql = ""
        Try
            sql = "DELETE FROM EMPRESA WHERE codempresa = " & valor_de_la_clave_primaria.Trim() & ""  'sintaxis sql
            cmd = New SqlCommand(sql, cnn)                    'crea nuevo comando SQL con sql y la conexxion1
            cmd.ExecuteNonQuery()                               'ejecuta el comando
        Catch
            Return False
            Exit Function
        Finally
            cnn.Close()
        End Try
        Return True
    End Function
    Public Function Modificar(ByVal codEmpresa As Integer, ByVal usugra As Integer, ByVal nomfantasia As String, ByVal nomcontribuyente As String, ByVal ruccontribuyente As String, ByVal nomrepresentante As String, ByVal rucrepresentante As String, ByVal desempresa As String, ByVal direccion As String, ByVal telefono As String, ByVal email As String, ByVal numregpatronal As String, ByVal retentor As Integer) As Boolean
        cnn = New SqlConnection
        cnn = ser.Abrir()
        Try
            'para actualizar la fecha
            b = System.DateTime.Parse(Now, formato)
            strFecha = b

            sql = ""
            sql = "UPDATE Empresa SET USUGRA='" & usugra & "', NOMFANTASIA = '" & nomfantasia & "', NOMCONTRIBUYENTE = '" & nomcontribuyente & " ', " & _
            " RUCCONTRIBUYENTE = '" & ruccontribuyente & " ', " & _
            " NOMREPRESENTANTE = '" & nomrepresentante & " ', " & _
            " RUCREPRESENTANTE = '" & rucrepresentante & " ', " & _
            " DESEMPRESA = '" & desempresa & "', DIRECCION = '" & direccion & "', TELEFONO ='" & telefono & "', EMAIL='" & email & "', NUMREGPATRONAL = '" & numregpatronal & "', RETENTOR = '" & retentor & "', FECGRA = '" & strFecha & "' WHERE CODEMPRESA='" & codEmpresa & "'"  'sintaxis sql
            cmd = New SqlCommand(sql, cnn)                    'crea nuevo comando SQL con sql y la conexxion1
            cmd.ExecuteNonQuery()                               'ejecuta el comando
        Catch
            Return False
            Exit Function
        Finally
            cnn.Close()
        End Try
        Return True
    End Function

    Public Function DsRecorrerCodigo() As DataSet
        cnn = New SqlConnection
        cnn = ser.Abrir()
        da = New SqlDataAdapter
        da = New SqlDataAdapter("SELECT CODEMPRESA FROM EMPRESA", cnn)
        ds = New DataSet
        da.Fill(ds)
        cnn.Close()
        Return ds
    End Function
    Public Function DsRecorrerDescripcion(ByVal codigo_de_busqueda As Long) As DataSet
        cnn = New SqlConnection
        cnn = ser.Abrir()
        da = New SqlDataAdapter
        da = New SqlDataAdapter("SELECT USUGRA, RTRIM(NOMFANTASIA) as NOMFANTASIA, RTRIM(NOMCONTRIBUYENTE) AS NOMCONTRIBUYENTE, RTRIM(RUCCONTRIBUYENTE) AS RUCCONTRIBUYENTE, RTRIM(NOMREPRESENTANTE) AS NOMREPRESENTANTE, RTRIM(RUCREPRESENTANTE) AS RUCREPRESENTANTE,  RTRIM(DESEMPRESA) AS DESEMPRESA, RTRIM(DIRECCION) AS DIRECCION, RTRIM(TELEFONO) AS TELEFONO, RTRIM(EMAIL) AS EMAIL, RTRIM(NUMREGPATRONAL) AS NUMREGPATRONAL, RETENTOR, FECGRA FROM EMPRESA WHERE CODEMPRESA = " & codigo_de_busqueda & "", cnn)
        ds = New DataSet
        da.Fill(ds)
        cnn.Close()
        Return ds
    End Function
    Public Function TodosLasEmpresas() As SqlDataReader
        Dim temp As Integer
        ser = New BD.ABD
        cnn = ser.Abrir
        sql = ""
        sql = "SELECT CODEMPRESA AS Codigo, rtrim(NOMFANTASIA) AS Descripcion FROM EMPRESA ORDER BY NOMFANTASIA"
        Try
            cmd = New SqlCommand(sql, cnn)
            dr = cmd.ExecuteReader()
            Return dr
        Finally
        End Try
    End Function
End Class
