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

    Public Function MMddyyyy(ByVal fecha_como_string As String) As String
        Dim tempo As String
        tempo = Format(CDate(fecha_como_string), "MM")
        tempo = tempo & "/" & Format(CDate(fecha_como_string), "dd")
        tempo = tempo & "/" & Format(CDate(fecha_como_string), "yyyy")
        Return tempo
    End Function
    Public Function ddMMyyyy(ByVal fecha_como_string As String) As String
        Dim tempo As String
        tempo = Format(CDate(fecha_como_string), "dd")
        tempo = tempo & "/" & Format(CDate(fecha_como_string), "MM")
        tempo = tempo & "/" & Format(CDate(fecha_como_string), "yyyy")
        Return tempo
    End Function
    Public Function fechahora() As String
        'Dim fecha_hoy, temp, hora As String
        'fecha_hoy = Now()
        'hora = TimeOfDay()
        'hora = Cadenas.Truncar(hora, 8)
        'temp = ddMMyyyy(fecha_hoy) + " " + hora
        'Return temp
        b = Now.Hour & ":" & Now.Minute & ":" & Now.Second
        a = Now.Day & "/" & Now.Month & "/" & Now.Year
        c = a & " " & b
        Return c
    End Function
    Public Function Maximo(ByVal nombre_campo As String, ByVal nombre_tabla As String) As Long
        ser = New BD.ABD
        cnn = ser.Abrir
        sql = ""
        sql = "SELECT MAX(" & nombre_campo & ") AS maximo FROM " & nombre_tabla & ""
        Try
            cmd = New SqlCommand(sql, cnn)
            dr = cmd.ExecuteReader()
            Try
                dr.Read()
                Return dr("maximo")
            Catch
                Return 0
            Finally
                dr.Close()
                cnn.Close()
            End Try
        Finally
        End Try
    End Function
    Public Function Color(ByVal estilo As Integer) As String
        Select Case estilo
            Case 1
                Return "230, 230, 230"
            Case 2
                Return "230, 130, 230"
            Case 3
                Return "230, 230, 130"
            Case 4
                Return "130, 230, 230"
            Case 5
                Return "130, 130, 130"
            Case 6
                Return "130, 150, 180"
            Case 7
                Return "60, 90, 160"
        End Select
    End Function
    Public Function MaximoMas1(ByVal nombre_campo As String, ByVal nombre_tabla As String, ByVal numcnn As SqlConnection) As Long
        Dim valor As Long = 1
        sql = ""
        sql = "SELECT MAX(" & nombre_campo & ") AS maximo FROM " & nombre_tabla & ""
        Try
            cmd = New SqlCommand(sql, numcnn)
            dr = cmd.ExecuteReader()
            Try
                dr.Read()
                valor = dr("maximo") + 1
                Return valor
            Catch
                'para evitar la aparición de errores en el caso de que haya registros
                Return valor
                dr.Close()
                Exit Try
            Finally
                dr.Close()
                'numcnn.Close()
            End Try
        Finally
        End Try

    End Function
    Public Function Conectar()
        cnn = ser.Abrir
        cnn.Close()
    End Function
    Public Function usuario(ByVal nombre_usuario As String, ByVal password As String, ByVal codigo_empresa As Long) As SqlDataReader
        Dim temp As Integer
        ser = New BD.ABD
        cnn = ser.Abrir
        sql = ""
        sql = "SELECT CODUSUARIO AS codigo, CODEMPRESA AS empresa, DESUSUARIO AS usuario, PASSUSUARIO as password, NIVELCUENTA as nivel FROM USUARIO WHERE DESUSUARIO = '" & nombre_usuario & "' AND PASSUSUARIO = '" & password & "' AND CODEMPRESA = '" & codigo_empresa & "'"
        Try
            cmd = New SqlCommand(sql, cnn)
            dr = cmd.ExecuteReader()
            Return dr
        Finally
        End Try
    End Function
    Public Function Buscar(ByVal nombre_campo_codigo As String, ByVal nombre_campo_descripcion As String, ByVal nombre_tabla As String, Optional ByVal Orden As Integer = 0) As SqlDataReader
        Dim temp As Integer
        ser = New BD.ABD
        cnn = ser.Abrir
        sql = ""
        Select Case Orden
            Case 0
                sql = "SELECT " & nombre_campo_codigo & " as Codigo, " & nombre_campo_descripcion & " AS Descripcion FROM " & nombre_tabla & " ORDER BY " & nombre_campo_codigo & ""
            Case 1
                sql = "SELECT " & nombre_campo_codigo & " as Codigo, " & nombre_campo_descripcion & " AS Descripcion FROM " & nombre_tabla & " ORDER BY " & nombre_campo_descripcion & ""
        End Select
        Try
            cmd = New SqlCommand(sql, cnn)
            dr = cmd.ExecuteReader()
            Return dr
        Finally
        End Try
    End Function
    Public Function Empresa() As DataSet
        cnn = New SqlConnection
        cnn = ser.Abrir()
        sql = ""
        sql = "SELECT CODEMPRESA as Codigo, rtrim(NOMFANTASIA) as Nombre FROM EMPRESA ORDER BY NOMFANTASIA"
        da = New SqlDataAdapter(sql, cnn)
        ds = New DataSet
        da.Fill(ds)
        cnn.Close()
        Return ds
    End Function
    Public Function Sucursal(ByVal empresa As Long) As DataSet
        cnn = New SqlConnection
        cnn = ser.Abrir()
        sql = ""
        sql = "select rtrim(numsucursal) + ' ' + rtrim(dessucursal) as Nombre, codsucursal as Codigo from sucursal where codsucursal = '" & empresa & "' ORDER BY DESSUCURSAL"
        da = New SqlDataAdapter(sql, cnn)
        ds = New DataSet
        da.Fill(ds)
        cnn.Close()
        Return ds
    End Function
    Public Function Validar_Tabla(ByVal nombre_campo As String, ByVal nombre_tabla As String, ByVal campo_buscado As String, ByVal variable_ingresada As String, ByVal codigo_empresa As Long) As Long
        ser = New BD.ABD
        cnn = ser.Abrir
        sql = ""
        sql = "SELECT MAX(" & nombre_campo & ") AS maximo FROM " & nombre_tabla & " where " & campo_buscado & " = '" & variable_ingresada & "' and codempresa = '" & codigo_empresa & "' "
        Try
            cmd = New SqlCommand(sql, cnn)
            dr = cmd.ExecuteReader()
            Try
                dr.Read()
                Return dr("maximo")
            Catch
                Return 0
            Finally
                dr.Close()
            End Try
        Finally
        End Try
    End Function
    Public Function Descripcion_Tabla(ByVal nombre_campo As String, ByVal nombre_tabla As String, ByVal campo_buscado As String, ByVal variable_ingresada As String, ByVal codigo_empresa As Long) As String
        ser = New BD.ABD
        cnn = ser.Abrir
        sql = ""
        sql = "SELECT " & nombre_campo & " AS descripcion FROM " & nombre_tabla & " where " & campo_buscado & " = '" & variable_ingresada & "' and codempresa = '" & codigo_empresa & "' "
        Try
            cmd = New SqlCommand(sql, cnn)
            dr = cmd.ExecuteReader()
            Try
                dr.Read()
                Return dr("descripcion")
            Catch
                Return 0
            Finally
                dr.Close()
            End Try
        Finally
        End Try
    End Function
End Class
