Module BancosDAO
    Private objCon As New Conexion
    Private cn As SqlClient.SqlConnection = objCon.Conectar

    Public Function Bancos() As DataTable
        Dim dt As New DataTable
        Dim query As String
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If
        Dim da As SqlClient.SqlDataAdapter

        query = "SELECT
	T0.[CodBanco]
	,T0.[NombreBanco]
	,T0.[CodPais]
FROM [dbo].[BAN0] T0"

        da = New SqlClient.SqlDataAdapter(query, cn)
        da.Fill(dt)

        Return dt
    End Function
    Public Function CuentasBancarias(ByVal pCodEmpresa As String, pCodBanco As String) As DataTable
        Dim dt As New DataTable
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If
        Dim query As String
        Dim da As SqlClient.SqlDataAdapter
        Dim cmd As New SqlClient.SqlCommand

        query = "SELECT
	T0.[Id]
	,T0.[CodBanco]
	,T0.[CodEmpr]
	,T0.[NumCuenta]
	,T0.[Activo]
	,T0.[NombreCuenta]
FROM [dbo].[BAN1] T0
WHERE
	T0.[CodEmpr]='" & pCodEmpresa & "'
	AND T0.[CodBanco]='" & pCodBanco & "'"

        da = New SqlClient.SqlDataAdapter(query, cn)

        da.Fill(dt)

        Return dt
    End Function
    Public Function ObtieneCuentaContable(ByVal pCodigoEmpresa As String, ByVal pCodigoBanco As String, ByVal pCuentaBancaria As String) As String
        Dim r As String
        If cn Is Nothing Then
            cn = objCon.Conectar
        End If
        If cn.State = ConnectionState.Open Then
            cn.Close()
            cn.Dispose()
            cn = objCon.Conectar
            cn.Open()
        Else
            cn = objCon.Conectar
            cn.Open()
        End If
        Dim cmd As SqlClient.SqlCommand
        Dim query As String

        If Not (String.IsNullOrEmpty(pCodigoEmpresa) And String.IsNullOrEmpty(pCodigoBanco) And String.IsNullOrEmpty(pCuentaBancaria)) Then
            query = "SELECT T0.CuentaContable FROM BAN1 T0 WHERE T0.CodBanco=@CodBanco AND T0.CodEmpr=@CodEmpr AND T0.NumCuenta=@NumCuenta"
            cmd = New SqlClient.SqlCommand
            cmd.CommandText = query
            cmd.Parameters.Add("@CodBanco", SqlDbType.VarChar).Value = pCodigoBanco
            cmd.Parameters.Add("@CodEmpr", SqlDbType.VarChar).Value = pCodigoEmpresa
            cmd.Parameters.Add("@NumCuenta", SqlDbType.VarChar).Value = pCuentaBancaria


            cmd.Connection = cn
            r = cmd.ExecuteScalar.ToString()

        End If

        Return r
    End Function
End Module