Imports System.Data.SqlClient

Public Class Rendimientos




    Public Function TraerRendimientos(ByVal TipoFecha As String, ByVal FechaInicial As Date, ByVal FechaFinal As Date, ByVal Empresa As String, ByVal Instrumento As String, ByVal Renta As String, ByVal Lugar As String, ByVal Vigencia As String, ByVal AutEstado As String, ByVal Fecha As Date, Lista As String) As DataTable
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim diccionario As New Dictionary(Of String, Collection)
        Dim ConAgrupacion As String = "NO"
        Dim dt As New DataTable
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet
        If Not String.IsNullOrEmpty(Lista) Then
            ConAgrupacion = "SI"
        End If

        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 sql = "SP_CargarRendimientos"


        Dim cmd As New SqlCommand(sql, cn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("@FechaInicial", SqlDbType.Date).Value = FechaInicial
        cmd.Parameters.Add("@FechaFinal", SqlDbType.Date).Value = FechaFinal
        cmd.Parameters.Add("@Empresa", SqlDbType.VarChar).Value = Empresa
        cmd.Parameters.Add("@Instrumento ", SqlDbType.VarChar).Value = Instrumento
        cmd.Parameters.Add("@TipoRenta", SqlDbType.VarChar).Value = Renta
        cmd.Parameters.Add("@Nacionalidad", SqlDbType.VarChar).Value = Lugar
        cmd.Parameters.Add("@Vigencia", SqlDbType.VarChar).Value = Vigencia
        cmd.Parameters.Add("@Estado", SqlDbType.VarChar).Value = AutEstado
        cmd.Parameters.Add("@Fecha", SqlDbType.Date).Value = Fecha
        cmd.Parameters.Add("@TipoFecha", SqlDbType.VarChar).Value = TipoFecha
        cmd.Parameters.Add("@ConAgrupacion", SqlDbType.VarChar).Value = ConAgrupacion
        cmd.Parameters.Add("@Lista", SqlDbType.VarChar).Value = Lista

        da = New SqlDataAdapter(cmd)
        da.Fill(ds)
        dt = ds.Tables(0)
        cn.Close()
        Return dt

    End Function








    Public Function Instrumentos() As Dictionary(Of String, String)
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim diccionario As New Dictionary(Of String, 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 sql = "Select CodIF,Descrip from INFI"

        Dim cmd As New SqlCommand(sql, cn)
        Dim dr As SqlDataReader
        dr = cmd.ExecuteReader
        While dr.Read
            diccionario.Add(dr("CodIF").ToString, dr("Descrip").ToString)
        End While
        cn.Close()
        Return diccionario
    End Function

    Public Function Empresas() As Dictionary(Of String, String)
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim diccionario As New Dictionary(Of String, 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 sql = "select CodEmpr,Descrip from EMPR"

        Dim cmd As New SqlCommand(sql, cn)
        Dim dr As SqlDataReader
        dr = cmd.ExecuteReader
        While dr.Read
            diccionario.Add(dr("CodEmpr").ToString, dr("Descrip").ToString)
        End While
        cn.Close()
        Return diccionario
    End Function



    Public Function CargarPortafolioGeneral(ByVal Tipo As String) As DataTable
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet
        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 sql = "[dbo].[SP_PortafolioGeneral]"


        Dim cmd As New SqlCommand(sql, cn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("Tipo", SqlDbType.VarChar).Value = Tipo
        da = New SqlDataAdapter(cmd)

        da.Fill(ds)
        dt = ds.Tables(0)
        cn.Close()
        Return dt
    End Function




    Public Function CargarRendimientoDiario(ByVal FechaInicial As Date, ByVal FechaFinal As Date, ByVal TipoRendimiento As String,
     ByVal ListaEmpresa As String, ByVal ListaInstrumento As String, ByVal ListaNacionalidad As String, ByVal ListaEstado As String,
     ByVal ListaVigencia As String, ByVal ListaTipoRenta As String, ByVal ListaAgrupada As String, ByVal Agrupado As String) As DataTable
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet
        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 sql = "[dbo].[SP_CargarRendimientoDiario]"
        Dim cmd As New SqlCommand(sql, cn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("@FechaInicial", SqlDbType.Date).Value = FechaInicial
        cmd.Parameters.Add("@FechaFinal", SqlDbType.Date).Value = FechaFinal
        cmd.Parameters.Add("@TipoRendimiento", SqlDbType.VarChar).Value = TipoRendimiento








        cmd.Parameters.Add("@Empresas", SqlDbType.VarChar).Value = ListaEmpresa
        cmd.Parameters.Add("@Instrumentos", SqlDbType.VarChar).Value = ListaInstrumento
        cmd.Parameters.Add("@Nacionalidad", SqlDbType.VarChar).Value = ListaNacionalidad
        cmd.Parameters.Add("@Estado", SqlDbType.VarChar).Value = ListaEstado
        cmd.Parameters.Add("@Vigencia", SqlDbType.VarChar).Value = ListaVigencia
        cmd.Parameters.Add("@TipoRenta", SqlDbType.VarChar).Value = ListaTipoRenta
        cmd.Parameters.Add("@Lista", SqlDbType.VarChar).Value = ListaAgrupada
        cmd.Parameters.Add("@Agrupado", SqlDbType.VarChar).Value = Agrupado

        da = New SqlDataAdapter(cmd)
        da.Fill(ds)
        cn.Close()
        Return ds.Tables(0)
    End Function
End Class