Inversiones/DAO/InversionDAO.vb

Imports System.Data.SqlClient
Public Class InversionDAO


    'Private frmPIN As New frmPIN
    Private Operaciones As New Operaciones
    Dim CambiarEstArbCE As New CambiarEstArbCE



    Public Function ObtenerTipoInstrumentoFinanciero(ByVal vCodigoIF As String) As String
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim valor As String = String.Empty
        Dim sql As String
        Dim cmd As SqlCommand

        sql = "SELECT T0.Tipo FROM [dbo].[INFI] T0 where T0.CodIF=@CodIF"
        cmd = New SqlCommand
        cmd.CommandText = sql
        cmd.Parameters.Add("@CodIF", SqlDbType.VarChar).Value = vCodigoIF
        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

        cmd.Connection = cn
        valor = cmd.ExecuteScalar

        cmd.Dispose()
        cn.Close()
        Return valor
    End Function

    Public Function ResumenPorInstrumento() As DataTable
        Dim dt As DataTable = VistaInversiones_ValorNominal()





    End Function
    Public Function VistaInversiones_ValorNominal() As DataTable
        Dim oVistaInversiones As DataTable = Me.VistaInversiones
        Dim vCodigoInstrumento As String
        Dim vCodigoInversion As String
        Dim oInstrumentoDAO As Object
        Dim oInstrumentoCE As Object
        Dim vValorNominal As Double
        Dim vDataColumn As DataColumn

        vDataColumn = New DataColumn
        vDataColumn.ColumnName = "ValorNominal"
        vDataColumn.DataType = GetType(Double)
        oVistaInversiones.Columns.Add(vDataColumn)


        For Each r As DataRow In oVistaInversiones.Rows
            vCodigoInstrumento = r.Item("CodIF")
            vCodigoInversion = r.Item("CodInv")

            If vCodigoInstrumento = "ACC" Then
                oInstrumentoDAO = New AccionesDAO
                oInstrumentoCE = oInstrumentoDAO.CargarRegistro(vCodigoInversion)
                vValorNominal = oInstrumentoCE.ValorTransadoCompra
            End If

            r.Item("ValorNominal") = vValorNominal
            oInstrumentoDAO = Nothing
        Next


        Return oVistaInversiones
    End Function
    Private Function VistaInversiones() As DataTable
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim sql As String
        Dim cmd As SqlCommand

        Dim da As SqlDataAdapter
        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
        cmd = New SqlCommand


        sql = "SELECT [CodInv]
      ,[CodIF]
      ,[Descrip]
      ,[CodEmpr]
      ,[Empresa]
      ,[CodEmis]
      ,[Emisor]
      ,[CodCalRi]
      ,[Calificacion]
      ,[CodECalRi]
      ,[Calificadora]
      ,[CodPais]
      ,[Pais]
  FROM [dbo].[vInversiones]
"



        cmd.CommandText = sql
        cmd.Connection = cn

        da = New SqlDataAdapter(cmd.CommandText, cmd.Connection)
        da.Fill(dt)
        cn.Close()
        Return dt
    End Function
    Public Function ListaCodigosInversion() As DataTable
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim sql As String
        Dim da As SqlDataAdapter

        Dim dt As New DataTable

        sql = "SELECT   ROW_NUMBER() OVER(ORDER BY [CodInv] ),[DocId] FROM [dbo].[INV0] ORDER BY [CodInv]"
        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

        da = New SqlDataAdapter(sql, cn)
        da.Fill(dt)

        Return dt


    End Function


    Public Function ListaCodigosPropuesta() As DataTable
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim sql As String
        Dim da As SqlDataAdapter

        Dim dt As New DataTable

        sql = "SELECT 
[DocId]
,[CodEmpr]
,[Asunto]
,[CodIF]
,[MontoInv]
,[Precio]
,[Rendimiento]
,[Plazo]
,[PlazoFact]
,[Ingresos]
,[OFon]
,[CodEmis]
,[CodCalRi]
,[CodECalRi]
,[CodPais]
,[TipoMerc]
,[CodInv]
,[DocEst]
,[TipoOper]
,[FechaDoc]
,[AutEstado] 
FROM [dbo].[PIN0] ORDER BY [CodInv]"
        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

        da = New SqlDataAdapter(sql, cn)
        da.Fill(dt)

        Return dt
        dt.Dispose()
        da.Dispose()
        cn.Dispose()

    End Function

















    Public Function Eliminar(ByVal Codigo As String, ByVal Tabla As String, ByVal Validar As Boolean) As Integer
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim oCon As New Conexion

        Dim retorno As Integer
        Dim sql As String
        Dim cmd As SqlCommand
        Dim myTrans As SqlTransaction
        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

        myTrans = cn.BeginTransaction()
        Try


            sql = "DELETE FROM " + Tabla + "
      WHERE [CodInv]=@CodInv"


            cmd = New SqlCommand
            cmd.CommandText = sql
            cmd.Connection = cn
            cmd.Transaction = myTrans
            cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo


            retorno = cmd.ExecuteNonQuery

            If Not Validar Then



                If Codigo.IndexOf("LETE") > -1 Then
                    sql = "DELETE FROM [dbo].[LET0]
      WHERE [CodInv]=@CodInv"


                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans
                    cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo


                    retorno = cmd.ExecuteNonQuery
                End If

                If Codigo.IndexOf("PBUR") > -1 Then
                    sql = "DELETE FROM [dbo].[PBUR]
      WHERE [CodInv]=@CodInv"


                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans
                    cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo


                    retorno = cmd.ExecuteNonQuery
                End If

                If Codigo.IndexOf("VCN") > -1 Then
                    sql = "DELETE FROM [dbo].[VCN]
      WHERE [CodInv]=@CodInv"


                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans
                    cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo


                    retorno = cmd.ExecuteNonQuery
                End If

                If Codigo.IndexOf("CETE") > -1 Then
                    sql = "DELETE FROM [dbo].[CET0]
      WHERE [CodInv]=@CodInv"


                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans
                    cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo


                    retorno = cmd.ExecuteNonQuery
                End If

                If Codigo.IndexOf("ACC") > -1 Then
                    sql = "DELETE FROM [dbo].[ACC1]
      WHERE [CodInv]=@CodInv"


                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans
                    cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo


                    retorno = cmd.ExecuteNonQuery



                    sql = "DELETE FROM [dbo].[ACC0]
      WHERE [CodInv]=@CodInv"


                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans
                    cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo


                    retorno = cmd.ExecuteNonQuery
                End If

                If Codigo.IndexOf("CINV") > -1 Then
                    sql = "DELETE FROM [dbo].[CIN0]
      WHERE [CodInv]=@CodInv"


                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans
                    cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo


                    retorno = cmd.ExecuteNonQuery



                    sql = "DELETE FROM [dbo].[CIN1]
      WHERE [CodInv]=@CodInv"


                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans
                    cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo
                    retorno = cmd.ExecuteNonQuery

                End If

                If Codigo.IndexOf("BONO") > -1 Then
                    sql = "DELETE FROM [dbo].[BON0]
      WHERE [CodInv]=@CodInv"


                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans
                    cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo


                    retorno = cmd.ExecuteNonQuery



                    sql = "DELETE FROM [dbo].[BON1]
      WHERE [CodInv]=@CodInv"


                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans
                    cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo
                    retorno = cmd.ExecuteNonQuery
                End If

                If Codigo.IndexOf("EURB") > -1 Then
                    sql = "DELETE FROM [dbo].[EURB0]
      WHERE [CodInv]=@CodInv"


                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans
                    cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo


                    retorno = cmd.ExecuteNonQuery



                    sql = "DELETE FROM [dbo].[EURB1]
      WHERE [CodInv]=@CodInv"


                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans
                    cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo
                    retorno = cmd.ExecuteNonQuery
                End If

                If Codigo.IndexOf("TIT") > -1 Then
                    sql = "DELETE FROM [dbo].[TIT]
      WHERE [CodInv]=@CodInv"


                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans
                    cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo


                    retorno = cmd.ExecuteNonQuery



                    sql = "DELETE FROM [dbo].[TIT0_0]
      WHERE [CodInv]=@CodInv"


                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans
                    cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo
                    retorno = cmd.ExecuteNonQuery

                    sql = "DELETE FROM [dbo].[TIT0]
      WHERE [CodInv]=@CodInv"


                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans
                    cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo


                    retorno = cmd.ExecuteNonQuery



                    sql = "DELETE FROM [dbo].[TIT2]
      WHERE [CodInv]=@CodInv"


                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans
                    cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo
                    retorno = cmd.ExecuteNonQuery
                End If

                If Codigo.IndexOf("DAP") > -1 Then
                    sql = "DELETE FROM [dbo].[DAP0]
      WHERE [CodInv]=@CodInv"


                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans
                    cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo


                    retorno = cmd.ExecuteNonQuery



                    sql = "DELETE FROM [dbo].[DAP1]
      WHERE [CodInv]=@CodInv"


                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans
                    cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo
                    retorno = cmd.ExecuteNonQuery
                End If

                If Codigo.IndexOf("FUTU") > -1 Then
                    sql = "DELETE FROM [dbo].[FUT0]
      WHERE [CodInv]=@CodInv"


                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans
                    cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo
                    retorno = cmd.ExecuteNonQuery
                End If

                If Codigo.IndexOf("OPC") > -1 Then
                    sql = "DELETE FROM [dbo].[OPC0]
      WHERE [CodInv]=@CodInv"


                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans
                    cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo
                    retorno = cmd.ExecuteNonQuery
                End If

                If Codigo.IndexOf("PEMP") > -1 Then
                    sql = "DELETE FROM [dbo].[PEMP0]
      WHERE [CodInv]=@CodInv"


                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans
                    cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo
                    retorno = cmd.ExecuteNonQuery

                    sql = "DELETE FROM [dbo].[PEMP1]
      WHERE [CodInv]=@CodInv"


                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans
                    cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo
                    retorno = cmd.ExecuteNonQuery
                End If

                If Codigo.IndexOf("PPER") > -1 Then


                    sql = "DELETE FROM [dbo].[PPER0]
      WHERE [CodInv]=@CodInv"


                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans
                    cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo
                    retorno = cmd.ExecuteNonQuery

                    sql = "DELETE FROM [dbo].[PPER1]
      WHERE [CodInv]=@CodInv"


                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans
                    cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo
                    retorno = cmd.ExecuteNonQuery


                    sql = "DELETE FROM [dbo].[PPER2]
      WHERE [CodInv]=@CodInv"


                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans
                    cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo
                    retorno = cmd.ExecuteNonQuery
                End If

                If Codigo.IndexOf("REPOVENTA") > -1 Then
                    sql = "DELETE FROM [dbo].[REP0VENTA]
      WHERE [CodInv]=@CodInv"


                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans
                    cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo
                    retorno = cmd.ExecuteNonQuery

                ElseIf Codigo.IndexOf("REPO") > -1 Then
                    sql = "DELETE FROM [dbo].[REP0]
      WHERE [CodInv]=@CodInv"


                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans
                    cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo
                    retorno = cmd.ExecuteNonQuery

                End If










            End If



            myTrans.Commit()

        Catch ex As Exception
            myTrans.Rollback()
            MsgBox(ex.Message)
            Return 1
        End Try
        cn.Close()

        Return 0


    End Function




    Public Function EliminarPropuesta(ByVal CodInv As String) As Integer

        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim valor As String = String.Empty
        Dim sql As String
        Dim cmd As SqlCommand


        sql = "Delete from [dbo].[PIN0] where [CodInv]=@CodInv"

        cmd = New SqlCommand
        cmd.CommandText = sql
        cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = CodInv.ToString()
        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

        cmd.Connection = cn
        valor = cmd.ExecuteScalar

        cmd.Dispose()
        cmd.Dispose()
        cmd.Dispose()


        Return 0


    End Function







    Public Function NuevoConReportoVenta(ByVal oInversionesCE As InversionesCE, ByVal Diccionario As Dictionary(Of String, Collection), ByVal CodigoReporto As String, ByVal CodigoAsociacion As String, ByRef EstadoReporto As String, ByRef ColUnionTablas As Collection)
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim retorno As Integer


        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 vPuedoContinuar As Boolean = True
        Dim vCodigoNuevo As String
        vCodigoNuevo = oInversionesCE.CodigoIversion
        '        vCodigoNuevo = SeriesDAO.SiguienteCodigoInversion(oInversionesCE.CodigoEmpresa, oInversionesCE.CodigoInstrumentoFinanciero)

        If oInversionesCE Is Nothing Then

            vPuedoContinuar = False
        Else

            If Me.ComprobarCodigoinversion(vCodigoNuevo) > 0 Then
                vPuedoContinuar = False
                MsgBox("Ya existe una inversión con el mismo código.")
            End If
        End If

        oInversionesCE.CodigoIversion = vCodigoNuevo
        If vPuedoContinuar Then


            Dim sql As String
            Dim cmd As SqlCommand
            Dim myTrans As SqlTransaction
            myTrans = cn.BeginTransaction()
            Dim vRetorno As Integer = 0
            Try




                sql = "INSERT INTO [dbo].[INV0]
([CodInv]
,[CodEmpr]
,[CodIF]
,[CodEmis]
,[CodCalRi]
,[CodECalRi]
,[Coment]
,[TipoOper]
,[DocBase]
,[Plazo]
,[PlazoFact]
,[TipoMerc]
,[CodPais]
,[NombreManIF]           
,[CodManIF]
,[Periodicidad]
,[TipTasa]           
,[TipRenta]
,[DocEst]
,[OFon]
,[EstadoProceso]
,[FechaDoc]
,[EstadoReporto]
)
VALUES
(
@CodInv
,@CodEmpr
,@CodIF
,@CodEmis
,@CodCalRi
,@CodECalRi
,@Coment
,@TipoOper
,@DocBase
,@Plazo
,@PlazoFact
,@TipoMerc
,@CodPais
,@NombreManIF
,@CodManIF
,@Periodicidad
,@TipTasa
,@TipRenta
,@DocEst
,@OFon
,@EstadoProceso
,@FechaDoc
,@EstadoReporto
)"


                cmd = New SqlCommand

                With cmd.Parameters
                    .Add("@CodInv", SqlDbType.VarChar).Value = vCodigoNuevo.ToString
                    .Add("@CodEmpr", SqlDbType.VarChar).Value = oInversionesCE.CodigoEmpresa.ToString
                    .Add("@CodIF", SqlDbType.VarChar).Value = oInversionesCE.CodigoInstrumentoFinanciero.ToString
                    .Add("@CodEmis", SqlDbType.Int).Value = oInversionesCE.CodigoEmisor
                    .Add("@CodCalRi", SqlDbType.VarChar).Value = oInversionesCE.CodigoCalificacionDeRiesgo.ToString
                    .Add("@CodECalRi", SqlDbType.VarChar).Value = oInversionesCE.CodigoEmpresaCalificadora.ToString
                    .Add("@Coment", SqlDbType.VarChar).Value = oInversionesCE.Comentarios.ToString
                    .Add("@TipoOper", SqlDbType.VarChar).Value = oInversionesCE.TipoOperacion.ToString
                    .Add("@DocBase", SqlDbType.Int).Value = oInversionesCE.DocumentoBase
                    .Add("@TipoMerc", SqlDbType.VarChar).Value = oInversionesCE.TipoMercado.ToString
                    .Add("@Plazo", SqlDbType.VarChar).Value = oInversionesCE.PlazoNumero.ToString
                    .Add("@PlazoFact", SqlDbType.VarChar).Value = oInversionesCE.PlazoFactor.ToString
                    .Add("@CodPais", SqlDbType.VarChar).Value = oInversionesCE.Pais.ToString
                    .Add("@NombreManIF", SqlDbType.VarChar).Value = oInversionesCE.NombreManual.ToString
                    .Add("@CodManIF", SqlDbType.VarChar).Value = oInversionesCE.CodigoManual.ToString
                    .Add("@Periodicidad", SqlDbType.VarChar).Value = oInversionesCE.Periodicidad.ToString
                    .Add("@TipTasa", SqlDbType.VarChar).Value = oInversionesCE.TipoTasa.ToString
                    .Add("@TipRenta", SqlDbType.VarChar).Value = oInversionesCE.TipoRenta.ToString
                    .Add("@DocEst", SqlDbType.VarChar).Value = oInversionesCE.EstadoDocumento.ToString
                    .Add("@OFon", SqlDbType.VarChar).Value = oInversionesCE.OFondo.ToString
                    .Add("@EstadoProceso", SqlDbType.VarChar).Value = oInversionesCE.EstadoProceso.ToString
                    .Add("@FechaDoc", SqlDbType.Date).Value = oInversionesCE.FechaDocumento

                    .Add("@EstadoReporto", SqlDbType.VarChar).Value = EstadoReporto


                End With


                cmd.CommandText = sql
                cmd.Connection = cn
                cmd.Transaction = myTrans
                retorno = cmd.ExecuteNonQuery





                '########################################################3'

                sql = "DELETE FROM REP0VENTA
                WHERE [CodInv]=@CodInv "


                cmd = New SqlCommand
                cmd.CommandText = sql
                cmd.Connection = cn
                cmd.Transaction = myTrans
                cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = CodigoReporto
                retorno = cmd.ExecuteNonQuery








                sql = "INSERT INTO ReportoGenerado(
                                    [CodigoAsociacion]
                                   ,[FechaCreado]
                                   ,[CodigoReporto])
                                    values(
                                    @CodigoAsociacion
                                   ,@FechaCreado
                                   ,@CodigoReporto



                )"


                cmd = New SqlCommand
                cmd.CommandText = sql
                cmd.Connection = cn
                cmd.Transaction = myTrans
                cmd.Parameters.Add("@CodigoAsociacion", SqlDbType.VarChar).Value = CodigoAsociacion
                cmd.Parameters.Add("@FechaCreado", SqlDbType.VarChar).Value = Date.Now.Date
                cmd.Parameters.Add("@CodigoReporto", SqlDbType.VarChar).Value = CodigoReporto
                retorno = cmd.ExecuteNonQuery


                If Not Diccionario Is Nothing Then


                    For Each DatosDiccionario In Diccionario
                        Dim Coleccion As Collection = DatosDiccionario.Value








                        sql = "INSERT INTO REP0VENTA    (
                           [CodInv]
                            ,[codigo]
                            ,[Nombre]
                            ,[FCompra]
                            ,[ValTrans]
                            ,[Dias]
                            ,[FVenc]
                            ,[Rend]
                            ,[CostoTransfr]
                            ,[ComisionCasa]
                            ,[ComisionBolsa]
                            ,[Casa]
                            ,[IOF]
                            ,[Renta]
                            ,[Base]
                            ,[ComisionCasaValor]
                            ,[ComisionBolsaValor]
                            ,[IOFValor]
                            ,[OtrosCostos]
                            ,[TotalCostos]
                            ,[MontoLiq]
                            ,[IntGenerar]
                            ,[RendAntImp]
                            ,[RendDesImp]
                            ,[ImpLiq]
                            ,[IngrNet]
                            ,[ValorNet]
                            ,[EstadoIOF]
                            ,[EstadoRenta]
                          

                ) VALUES(
                           @CodInv
                            ,@codigo
                            ,@Nombre
                            ,@FCompra
                            ,@ValTrans
                            ,@Dias
                            ,@FVenc
                            ,@Rend
                            ,@CostoTransfr
                            ,@ComisionCasa
                            ,@ComisionBolsa
                            ,@Casa
                            ,@IOF
                            ,@Renta
                            ,@Base
                            ,@ComisionCasaValor
                            ,@ComisionBolsaValor
                            ,@IOFValor
                            ,@OtrosCostos
                            ,@TotalCostos
                            ,@MontoLiq
                            ,@IntGenerar
                            ,@RendAntImp
                            ,@RendDesImp
                            ,@ImpLiq
                            ,@IngrNet
                            ,@ValorNet
                            ,@EstadoIOF
                            ,@EstadoRenta
                        
                )
                    "










                        cmd = New SqlCommand
                        cmd.CommandText = sql
                        cmd.Connection = cn
                        cmd.Transaction = myTrans



                        With cmd.Parameters
                            .Add("@CodInv", SqlDbType.VarChar).Value = CodigoReporto.ToString
                            .Add("@Nombre", SqlDbType.VarChar).Value = Coleccion(1).ToString
                            .Add("@FCompra", SqlDbType.Date).Value = Operaciones.ConvertirFecha(Coleccion(2).ToString)
                            .Add("@ValTrans", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(3).ToString)
                            .Add("@Dias", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(4).ToString)
                            .Add("@FVenc", SqlDbType.Date).Value = Operaciones.ConvertirFecha(Coleccion(5).ToString)
                            .Add("@Rend", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(6).ToString)
                            .Add("@CostoTransfr", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(7).ToString)
                            .Add("@codigo", SqlDbType.VarChar).Value = Coleccion(8).ToString
                            .Add("@ComisionCasa", SqlDbType.Decimal).Value = Operaciones.ConvertirDecimal(Coleccion(9).ToString)
                            .Add("@ComisionBolsa", SqlDbType.Decimal).Value = Operaciones.ConvertirDecimal(Coleccion(10).ToString)
                            .Add("@Casa", SqlDbType.VarChar).Value = Coleccion(11).ToString
                            .Add("@IOF", SqlDbType.Bit).Value = CByte(Coleccion(12))
                            .Add("@Renta", SqlDbType.Bit).Value = CByte(Coleccion(13))
                            .Add("@Base", SqlDbType.Int).Value = DBNull.Value
                            .Add("@ComisionCasaValor", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(14).ToString)
                            .Add("@ComisionBolsaValor", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(15).ToString)
                            .Add("@IOFValor", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(16).ToString)
                            .Add("@OtrosCostos", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(17).ToString)
                            .Add("@TotalCostos", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(18).ToString)
                            .Add("@MontoLiq", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(19).ToString)
                            .Add("@IntGenerar", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(20).ToString)
                            .Add("@RendAntImp", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(21).ToString)
                            .Add("@RendDesImp", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(22).ToString)
                            .Add("@ImpLiq", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(23).ToString)
                            .Add("@IngrNet", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(24).ToString)
                            .Add("@ValorNet", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(25).ToString)
                            .Add("@EstadoIOF", SqlDbType.Bit).Value = CByte(Coleccion(27))
                            .Add("@EstadoRenta", SqlDbType.Bit).Value = CByte(Coleccion(28))







                        End With



                        retorno = cmd.ExecuteNonQuery





                    Next


                End If

                Dim PIdFlujo As Integer = 0

                ''''''''''''''''''
                For Each DicUnionTab As Dictionary(Of String, Object) In ColUnionTablas
                    Dim CodigoTitulos = Function()
                                            If DicUnionTab.ContainsKey("CodigoTitulos") Then
                                                Return DicUnionTab.Item("CodigoTitulos")
                                            Else
                                                Return String.Empty
                                            End If
                                        End Function


                    Dim IdFlujo = Function()
                                      If DicUnionTab.ContainsKey("IdFlujo") Then
                                          Return DicUnionTab.Item("IdFlujo")
                                      Else
                                          Return 0
                                      End If
                                  End Function


                    Dim IdLisRep = Function()
                                       If DicUnionTab.ContainsKey("IdLisRep") Then
                                           Return DicUnionTab.Item("IdLisRep")
                                       Else
                                           Return 0
                                       End If
                                   End Function

                    Dim ValorCodigoTitulos As String = CodigoTitulos().ToString
                    Dim ValorIdFlujo As Integer = Operaciones.ConvertirEntero(IdFlujo().ToString)
                    Dim ValorIdLisRep As Integer = Operaciones.ConvertirEntero(IdLisRep().ToString)
                    If PIdFlujo = 0 Then
                        PIdFlujo = ValorIdFlujo
                    End If

                    sql = "INSERT INTO REPOGEN(
                                     [CodigoAsociado]
                                    ,[CodigoReporto]
                                    ,[CodigoTitulo]
                                    ,[CodigoEstado]
                                    ,[Nombre]
                                    ,[Descrip]
                                    ,[IdFlujo]
                                    ,[IdLisRep]
)VALUES(
                                     @CodigoAsociado
                                    ,@CodigoReporto
                                    ,@CodigoTitulo
                                    ,@CodigoEstado
                                    ,@Nombre
                                    ,@Descrip
                                    ,@IdFlujo
                                    ,@IdLisRep

)"


                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans








                    cmd.Parameters.Add("@CodigoAsociado", SqlDbType.VarChar).Value = CodigoAsociacion
                    cmd.Parameters.Add("@CodigoReporto", SqlDbType.VarChar).Value = CodigoReporto
                    cmd.Parameters.Add("@CodigoTitulo", SqlDbType.VarChar).Value = ValorCodigoTitulos
                    cmd.Parameters.Add("@CodigoEstado", SqlDbType.VarChar).Value = "RC"
                    cmd.Parameters.Add("@Nombre", SqlDbType.VarChar).Value = "Reporto Creado"
                    cmd.Parameters.Add("@Descrip", SqlDbType.VarChar).Value = "Reporto Creado(En Espera de Generar Lista de Titulos de Inversion)"
                    cmd.Parameters.Add("@IdFlujo", SqlDbType.Int).Value = ValorIdFlujo
                    cmd.Parameters.Add("@IdLisRep", SqlDbType.Int).Value = ValorIdLisRep
                    retorno = cmd.ExecuteNonQuery




                    '########################################################################'





                    sql = "INSERT INTO TitTerm(
                                     [CodigoAsociado]
                                    ,[CodigoTitulo]
                                    ,[CodigoEstado]
                                    ,[Nombre]
                                    ,[Descrip]
                                    ,[IdFlujo]
                                    ,[IdLisRep]
)VALUES(
                                     @CodigoAsociado
                                    ,@CodigoTitulo
                                    ,@CodigoEstado
                                    ,@Nombre
                                    ,@Descrip
                                    ,@IdFlujo
                                    ,@IdLisRep

)"


                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans








                    cmd.Parameters.Add("@CodigoAsociado", SqlDbType.VarChar).Value = CodigoAsociacion
                    cmd.Parameters.Add("@CodigoTitulo", SqlDbType.VarChar).Value = ValorCodigoTitulos
                    cmd.Parameters.Add("@CodigoEstado", SqlDbType.VarChar).Value = "FIN"
                    cmd.Parameters.Add("@Nombre", SqlDbType.VarChar).Value = "Ciclo de Titulo Finalizado"
                    cmd.Parameters.Add("@Descrip", SqlDbType.VarChar).Value = "Ciclo de TItulo Finalizado(En Espera de Generar más)"
                    cmd.Parameters.Add("@IdFlujo", SqlDbType.Int).Value = ValorIdFlujo
                    cmd.Parameters.Add("@IdLisRep", SqlDbType.Int).Value = ValorIdLisRep
                    retorno = cmd.ExecuteNonQuery







                Next


                sql = "INSERT INTO SegundaFase(
                                     [CodigoReporto]
                                    ,[CodigoAsociado]
                                    ,[IdFlujo]
)VALUES(
                                     @CodigoReporto
                                    ,@CodigoAsociado
                                    ,@IdFlujo

)"
                cmd = New SqlCommand
                cmd.CommandText = sql
                cmd.Connection = cn
                cmd.Transaction = myTrans
                cmd.Parameters.Add("@CodigoReporto", SqlDbType.VarChar).Value = CodigoReporto
                cmd.Parameters.Add("@CodigoAsociado", SqlDbType.VarChar).Value = CodigoAsociacion
                cmd.Parameters.Add("@IdFlujo", SqlDbType.VarChar).Value = PIdFlujo

                retorno = cmd.ExecuteNonQuery








                myTrans.Commit()
                MsgBox("Datos Ingresados")
                frmColaTitulos.Inicializacion()
            Catch ex As Exception

                myTrans.Rollback()
                MsgBox(ex.Message)
            End Try
        End If
        cn.Close()

        Return retorno


    End Function



    Public Function Nuevo(ByVal oInversionesCE As InversionesCE) As Integer

        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim vRetorno As Integer = 0
        Dim vPuedoContinuar As Boolean = True
        Dim vCodigoNuevo As String
        vCodigoNuevo = oInversionesCE.CodigoIversion
        '        vCodigoNuevo = SeriesDAO.SiguienteCodigoInversion(oInversionesCE.CodigoEmpresa, oInversionesCE.CodigoInstrumentoFinanciero)

        If oInversionesCE Is Nothing Then

            vPuedoContinuar = False
        Else

            If Me.ComprobarCodigoinversion(vCodigoNuevo) > 0 Then
                vPuedoContinuar = False
                MsgBox("Ya existe una inversión con el mismo código.")
            End If
        End If

        oInversionesCE.CodigoIversion = vCodigoNuevo
        If vPuedoContinuar Then
            'VIENE UNA CE DE INVERCIONES
            Dim vQuery 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


            If cn.State = ConnectionState.Open Then
                Dim vTransaccion As SqlTransaction
                Dim cmd As SqlCommand = cn.CreateCommand
                vTransaccion = cn.BeginTransaction
                cmd.Connection = cn
                cmd.Transaction = vTransaccion
                Try




                    vQuery = "INSERT INTO [dbo].[INV0]
([CodInv]
,[CodEmpr]
,[CodIF]
,[CodEmis]
,[CodCalRi]
,[CodECalRi]
,[Coment]
,[TipoOper]
,[DocBase]
,[Plazo]
,[PlazoFact]
,[TipoMerc]
,[CodPais]
,[NombreManIF]           
,[CodManIF]
,[Periodicidad]
,[TipTasa]           
,[TipRenta]
,[DocEst]
,[OFon]
,[EstadoProceso]
,[FechaDoc]
,[CodigoAsociado]
,[EstadoReporto]

)
VALUES
(
@CodInv
,@CodEmpr
,@CodIF
,@CodEmis
,@CodCalRi
,@CodECalRi
,@Coment
,@TipoOper
,@DocBase
,@Plazo
,@PlazoFact
,@TipoMerc
,@CodPais
,@NombreManIF
,@CodManIF
,@Periodicidad
,@TipTasa
,@TipRenta
,@DocEst
,@OFon
,@EstadoProceso
,@FechaDoc
,@CodigoAsociado
,@EstadoReporto
)"
                    cmd.CommandText = vQuery

                    With cmd.Parameters
                        .Add("@CodInv", SqlDbType.VarChar).Value = vCodigoNuevo.ToString
                        .Add("@CodEmpr", SqlDbType.VarChar).Value = oInversionesCE.CodigoEmpresa.ToString
                        .Add("@CodIF", SqlDbType.VarChar).Value = oInversionesCE.CodigoInstrumentoFinanciero.ToString
                        .Add("@CodEmis", SqlDbType.Int).Value = oInversionesCE.CodigoEmisor
                        .Add("@CodCalRi", SqlDbType.VarChar).Value = oInversionesCE.CodigoCalificacionDeRiesgo.ToString
                        .Add("@CodECalRi", SqlDbType.VarChar).Value = oInversionesCE.CodigoEmpresaCalificadora.ToString
                        .Add("@Coment", SqlDbType.VarChar).Value = oInversionesCE.Comentarios.ToString
                        .Add("@TipoOper", SqlDbType.VarChar).Value = oInversionesCE.TipoOperacion.ToString
                        .Add("@DocBase", SqlDbType.Int).Value = oInversionesCE.DocumentoBase
                        .Add("@TipoMerc", SqlDbType.VarChar).Value = oInversionesCE.TipoMercado.ToString
                        .Add("@Plazo", SqlDbType.VarChar).Value = oInversionesCE.PlazoNumero.ToString
                        .Add("@PlazoFact", SqlDbType.VarChar).Value = oInversionesCE.PlazoFactor.ToString
                        .Add("@CodPais", SqlDbType.VarChar).Value = oInversionesCE.Pais.ToString
                        .Add("@NombreManIF", SqlDbType.VarChar).Value = oInversionesCE.NombreManual.ToString
                        .Add("@CodManIF", SqlDbType.VarChar).Value = oInversionesCE.CodigoManual.ToString
                        .Add("@Periodicidad", SqlDbType.VarChar).Value = oInversionesCE.Periodicidad.ToString
                        .Add("@TipTasa", SqlDbType.VarChar).Value = oInversionesCE.TipoTasa.ToString
                        .Add("@TipRenta", SqlDbType.VarChar).Value = oInversionesCE.TipoRenta.ToString
                        .Add("@DocEst", SqlDbType.VarChar).Value = oInversionesCE.EstadoDocumento.ToString
                        .Add("@OFon", SqlDbType.VarChar).Value = oInversionesCE.OFondo.ToString
                        .Add("@EstadoProceso", SqlDbType.VarChar).Value = oInversionesCE.EstadoProceso.ToString
                        .Add("@FechaDoc", SqlDbType.Date).Value = Date.Now.Date
                        .Add("@CodigoAsociado", SqlDbType.VarChar).Value = oInversionesCE.CodigoAsociado
                        .Add("@EstadoReporto", SqlDbType.VarChar).Value = oInversionesCE.EstadoReporto
                    End With

                    vRetorno = cmd.ExecuteNonQuery
                    oInversionesCE.IdFlujo += 1



                    vQuery = "INSERT INTO TitulosGenerarLREP(
                            [CodigoTitulo]
                            ,[CodigoEstado]
                            ,[Nombre]
                            ,[Descrip]
                            ,[IdProInv]
                            ,[IdFlujo]
                            )
                            VALUES(
                             @CodigoTitulo
                            ,@CodigoEstado
                            ,@Nombre
                            ,@Descrip
                            ,@IdProInv
                            ,@IdFlujo

                            )
                       "


                    cmd = New SqlCommand
                    cmd.CommandText = vQuery
                    cmd.Connection = cn
                    cmd.Transaction = vTransaccion
                    cmd.Parameters.Add("@CodigoTitulo", SqlDbType.VarChar).Value = vCodigoNuevo.ToString
                    cmd.Parameters.Add("@CodigoEstado", SqlDbType.VarChar).Value = "SEG"
                    cmd.Parameters.Add("@Nombre", SqlDbType.VarChar).Value = "Sin Asignar"
                    cmd.Parameters.Add("@Descrip", SqlDbType.VarChar).Value = "Sin Asignar(En Espera de Generar una Lista de Titulos para Reportear)"
                    cmd.Parameters.Add("@IdFlujo", SqlDbType.Int).Value = oInversionesCE.IdFlujo
                    cmd.Parameters.Add("@IdProInv", SqlDbType.Int).Value = oInversionesCE.IdProInv
                    vRetorno = cmd.ExecuteNonQuery





                    vQuery = "INSERT INTO IniFlu(
                            [IdFlujo]   
                           ,[CodigoAsociadoAnterior]
                           ,[CodigoTitulo]   
                            )
                            VALUES(
                            @IdFlujo
                           ,@CodigoAsociadoAnterior
                           ,@CodigoTitulo

                            )
                       "
                    If String.IsNullOrEmpty(oInversionesCE.CodigoAsociado) Then
                        oInversionesCE.CodigoAsociado = "Sin Pasado"
                    End If

                    cmd = New SqlCommand
                    cmd.CommandText = vQuery
                    cmd.Connection = cn
                    cmd.Transaction = vTransaccion
                    cmd.Parameters.Add("@IdFlujo", SqlDbType.Int).Value = oInversionesCE.IdFlujo
                    cmd.Parameters.Add("@CodigoTitulo", SqlDbType.VarChar).Value = vCodigoNuevo.ToString
                    cmd.Parameters.Add("@CodigoAsociadoAnterior", SqlDbType.VarChar).Value = oInversionesCE.CodigoAsociado
                    vRetorno = cmd.ExecuteNonQuery


                    '' #####################################################

                    Dim IniciarError As String = CambiarEstArbCE.ProcesarCambio(vCodigoNuevo.ToString, "CambiarAprobacion", cn, vTransaccion)
                    If IniciarError = "NO" Or IniciarError = "Error" Then
                        Throw New Exception
                    End If
                    '##############################################################'

                    If oInversionesCE.EstadoReporto = "SECUNDARIO" Then


                        vQuery = "INSERT INTO TitApro(
                            [CodigoAsociado]   
                           ,[CodigoTitulo]   
                           ,[CodigoEstado]   
                           ,[Nombre]   
                           ,[Descrip]   
                           ,[IdEA]
                           ,[IdFlujo]   
                            )
                            VALUES(
                            @CodigoAsociado   
                           ,@CodigoTitulo   
                           ,@CodigoEstado   
                           ,@Nombre   
                           ,@Descrip   
                           ,@IdEA
                           ,@IdFlujo   

                            )
                       "


                        cmd = New SqlCommand
                        cmd.CommandText = vQuery
                        cmd.Connection = cn
                        cmd.Transaction = vTransaccion
                        cmd.Parameters.Add("@CodigoAsociado", SqlDbType.VarChar).Value = oInversionesCE.CodigoAsociado
                        cmd.Parameters.Add("@CodigoTitulo", SqlDbType.VarChar).Value = vCodigoNuevo.ToString
                        cmd.Parameters.Add("@CodigoEstado", SqlDbType.VarChar).Value = "TA"
                        cmd.Parameters.Add("@Nombre", SqlDbType.VarChar).Value = "Titulo Aprobado"
                        cmd.Parameters.Add("@Descrip", SqlDbType.VarChar).Value = "Titulo Aprobado"
                        cmd.Parameters.Add("@IdEA", SqlDbType.Int).Value = oInversionesCE.IdEA
                        cmd.Parameters.Add("@IdFlujo", SqlDbType.Int).Value = oInversionesCE.IdFlujo

                        vRetorno = cmd.ExecuteNonQuery
                    End If




                    '###########################################################'


                    ''Ingresar codigo
                    vTransaccion.Commit()
                    cmd.Dispose()
                    vTransaccion.Dispose()
                    cn.Dispose()
                    cn.Close()
                Catch ex2 As Exception
                    vTransaccion.Rollback()
                    MsgBox("Error")
                    vRetorno = 0
                End Try




            Else
                'NADA?
                vRetorno = 0
            End If


        Else
            cn.Close()
            'NO VIENE
            vRetorno = -1
        End If


        Return vRetorno
    End Function







    Public Function Modificar(ByVal oCEINV As InversionesCE) As Integer
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim retorno As Integer
        Dim cmd As SqlCommand
        Dim sql As String

        sql = "UPDATE [dbo].[INV0]
   SET [Asunto] = @Asunto
      ,[CodEmpr] = @CodEmpr      
      ,[CodEmis] = @CodEmis
      ,[CodCalRi] = @CodCalRi
      ,[CodECalRi] = @CodECalRi
      ,[OFon] = @OFon
      ,[Coment] = @Coment
      ,[Just] = @Just
      ,[TipoOper] = @TipoOper            
      ,[Plazo] = @Plazo
      ,[PlazoFact] = @PlazoFact      
	  ,[FechaDoc] = @FechaDoc      
	  ,[TipoMerc] = @TipoMerc
      ,[CodPais] = @CodPais
      ,[NombreManIF] = @NombreManIF
      ,[CodManIF] = @CodManIF
      ,[Periodicidad] = @Periodicidad
      ,[TipTasa] = @TipTasa
      ,[TipRenta] = @TipRenta
      ,[DocEst]=@DocEst
      ,[EstadoProceso]=@EstadoProceso
 WHERE [CodInv]=@CodInv"

        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

        cmd = New SqlCommand
        cmd.CommandText = sql
        cmd.Connection = cn

        With cmd.Parameters
            .Add("@Asunto", SqlDbType.VarChar).Value = String.Empty
            .Add("@CodEmpr", SqlDbType.VarChar).Value = oCEINV.CodigoEmpresa
            .Add("@CodEmis", SqlDbType.VarChar).Value = oCEINV.CodigoEmisor
            .Add("@CodCalRi", SqlDbType.VarChar).Value = oCEINV.CodigoCalificacionDeRiesgo
            .Add("@CodECalRi", SqlDbType.VarChar).Value = oCEINV.CodigoEmpresaCalificadora
            .Add("@OFon", SqlDbType.VarChar).Value = oCEINV.OFondo
            .Add("@Coment", SqlDbType.VarChar).Value = oCEINV.Comentarios
            .Add("@Just", SqlDbType.VarChar).Value = String.Empty
            .Add("@TipoOper", SqlDbType.VarChar).Value = oCEINV.TipoOperacion
            .Add("@Plazo", SqlDbType.Int).Value = oCEINV.PlazoNumero
            .Add("@PlazoFact", SqlDbType.VarChar).Value = oCEINV.PlazoFactor
            .Add("@FechaDoc", SqlDbType.Date).Value = oCEINV.FechaDocumento
            .Add("@TipoMerc", SqlDbType.VarChar).Value = oCEINV.TipoMercado
            .Add("@CodPais", SqlDbType.VarChar).Value = oCEINV.Pais
            .Add("@NombreManIF", SqlDbType.VarChar).Value = oCEINV.NombreManual
            .Add("@CodManIF", SqlDbType.VarChar).Value = oCEINV.CodigoManual
            .Add("@Periodicidad", SqlDbType.VarChar).Value = oCEINV.Periodicidad
            .Add("@TipTasa", SqlDbType.VarChar).Value = oCEINV.TipoTasa
            .Add("@TipRenta", SqlDbType.VarChar).Value = oCEINV.TipoRenta
            .Add("@CodInv", SqlDbType.VarChar).Value = oCEINV.CodigoIversion
            .Add("@DocId", SqlDbType.Int).Value = oCEINV.IdDeDocumento

            .Add("@DocEst", SqlDbType.VarChar).Value = oCEINV.EstadoDocumento

            .Add("@EstadoProceso", SqlDbType.VarChar).Value = oCEINV.EstadoProceso
        End With

        retorno = cmd.ExecuteNonQuery
        cn.Close()
        Return retorno
    End Function
    Public Function ComprobarCodigoinversion(CodigoInversion As String) As Integer
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim vRetorno As Integer
        Dim sql As String
        Dim cmd As SqlCommand = New SqlCommand
        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
        sql = "SELECT COUNT('A') FROM [dbo].[INV0] WHERE CodInv=@CodInv"
        cmd.CommandText = sql
        cmd.Connection = cn
        cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = CodigoInversion


        vRetorno = cmd.ExecuteScalar

        cn.Close()
        Return vRetorno
    End Function

    Public Function Buscar(ByVal DiccionarioColecciones As Dictionary(Of String, Collection)) As DataSet
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim ds As New DataSet
        Dim da As SqlDataAdapter
        cn = objCon.Conectar
        Dim Col_Empresas As New Collection
        Dim Col_Emisor As New Collection
        Dim Col_EstDocumento As New Collection
        Dim Col_Instrumento As New Collection
        Dim Col_CalRiesgo As New Collection
        Dim Col_TipOperacion As New Collection
        Dim Col_OriFond As New Collection
        Dim Col_CalERiesgo As New Collection
        Dim Col_Aut As New Collection
        Dim Col_Plazo As New Collection
        Dim Col_Pais As New Collection
        Dim Col_TipMercado As New Collection
        Dim Vacio As Boolean = False


        For Each Item In DiccionarioColecciones
            If Item.Key = "Empresa" Then
                Col_Empresas = Item.Value
            ElseIf Item.Key = "Emisor" Then

                Col_Emisor = Item.Value

            ElseIf Item.Key = "EstDocumento" Then
                Col_EstDocumento = Item.Value



            ElseIf Item.Key = "Instrumento" Then

                Col_Instrumento = Item.Value


            ElseIf Item.Key = "CalRiesgo" Then

                Col_CalRiesgo = Item.Value


            ElseIf Item.Key = "TipOperacion" Then
                Col_TipOperacion = Item.Value


            ElseIf Item.Key = "OriFond" Then
                Col_OriFond = Item.Value


            ElseIf Item.Key = "CalERiesgo" Then

                Col_CalERiesgo = Item.Value


            ElseIf Item.Key = "Aut" Then

                Col_Aut = Item.Value


            ElseIf Item.Key = "Plazo" Then

                Col_Plazo = Item.Value

            ElseIf Item.Key = "Pais" Then

                Col_Pais = Item.Value

            ElseIf Item.Key = "TipMercado" Then
                Col_TipMercado = Item.Value
            End If
        Next




        If Col_Empresas.Count = 0 Or Col_Emisor.Count = 0 Or Col_EstDocumento.Count = 0 Or Col_OriFond.Count = 0 Or Col_CalERiesgo.Count = 0 Or Col_Aut.Count = 0 Or Col_Plazo.Count = 0 Or Col_Pais.Count = 0 Or Col_TipMercado.Count = 0 Or Col_Instrumento.Count = 0 Then
            Vacio = True
        End If















        Dim sql As String = "select 
        CAST(DocId as varchar) as 'Id'
        ,CAST(CodInv as varchar) as 'Código de Propuesta'
        ,CAST(DocEst as varchar) as 'Estado'
        ,CAST(Asunto as varchar) as 'Asunto'
        ,CAST(CodEmpr as varchar) as 'Empresa'
        ,CAST(CodIF as varchar) as 'Instrumento Financiero'
        ,CAST(CodEmis as varchar) as 'Emisor'
        ,CAST(CodCalRi as varchar) as 'Calificación de Riesgo'
        ,CAST(CodECalRi as varchar) as 'Empresa Calificadora'
        ,CAST(OFon as varchar) as 'Origen de Fondos'
        ,CAST(Coment as varchar) as 'Comentarios'
        ,CAST(Just as varchar) as 'Justificaciones'
        ,CAST(TipoOper as varchar)  as 'Tipo de Operacion'
        ,CAST(DocBase as varchar) as 'Base de Calculo'
        ,CAST(MontoInv as varchar) as 'Monto de Inversion'
        ,CAST(Precio as varchar) as 'Precio'
        ,CAST(Rendimiento as varchar) as 'Rendimiento'
        ,CAST(Plazo as varchar)   as 'Cantidad en Plazo'
        ,CAST(PlazoFact as varchar) as 'Tipo de Plazo'
        ,CAST(Ingresos as varchar) as 'Ingresos'
        ,CAST(FechaDoc as varchar) as 'Fecha de Creación'
        ,CAST(AutEstado as varchar) as 'Estado de Autorización'
        ,CAST(TipoMerc as varchar) as 'Tipo de Mercado'
        ,CAST(CodPais as varchar) as 'Pais'
        from INV0 
 "

        If Vacio Then
            sql += " where 0=1"

        Else
            sql += " where 1=1"
        End If




        Dim TodosEmpresas As Boolean = False
        Dim TodosEmisor As Boolean = False
        Dim TodosEstDocumento As Boolean = False
        Dim TodosInstrumento As Boolean = False
        Dim TodosCalRiesgo As Boolean = False
        Dim TodosTipOperacion As Boolean = False
        Dim TodosOriFond As Boolean = False
        Dim TodosCalERiesgo As Boolean = False
        Dim TodosAut As Boolean = False
        Dim TodosPlazo As Boolean = False
        Dim TodosPais As Boolean = False
        Dim TodosTipMercado As Boolean = False


        For Each Item In Col_Empresas
            If Not Item.IndexOf("Todos") = -1 Then
                TodosEmpresas = True
            End If
        Next
        For Each Item In Col_Emisor
            If Not Item.IndexOf("Todos") = -1 Then
                TodosEmisor = True
            End If
        Next
        For Each Item In Col_EstDocumento
            If Not Item.IndexOf("Todos") = -1 Then
                TodosEstDocumento = True
            End If
        Next
        For Each Item In Col_Instrumento
            If Not Item.IndexOf("Todos") = -1 Then
                TodosInstrumento = True
            End If
        Next
        For Each Item In Col_CalRiesgo
            If Not Item.IndexOf("Todos") = -1 Then
                TodosCalRiesgo = True
            End If
        Next
        For Each Item In Col_TipOperacion
            If Not Item.IndexOf("Todos") = -1 Then
                TodosTipOperacion = True
            End If
        Next
        For Each Item In Col_OriFond
            If Not Item.IndexOf("Todos") = -1 Then
                TodosOriFond = True
            End If
        Next
        For Each Item In Col_CalERiesgo
            If Not Item.IndexOf("Todos") = -1 Then
                TodosCalERiesgo = True
            End If
        Next
        For Each Item In Col_Aut
            TodosAut = True
        Next
        For Each Item In Col_Plazo
            If Not Item.IndexOf("Todos") = -1 Then
                TodosPlazo = True
            End If
        Next
        For Each Item In Col_Pais
            If Not Item.IndexOf("Todos") = -1 Then
                TodosPais = True
            End If
        Next
        For Each Item In Col_TipMercado

            If Not Item.IndexOf("Todos") = -1 Then
                TodosTipMercado = True
            End If
        Next



        Dim OperadorLogico As String = String.Empty
        OperadorLogico = " And ("
        Dim Fin As String = String.Empty



        If Not TodosEmpresas Then


            For Each Item In Col_Empresas
                Dim Cadena As String = Col_Empresas.ToString


                Dim NombreItem As String = Item.ToString.Trim
                Dim ConsultaExtra As String = "" + OperadorLogico + " CodEmpr like '%" + NombreItem + "%'"
                sql += ConsultaExtra
                OperadorLogico = " Or"
                Fin = ")"
            Next

            If Not String.IsNullOrEmpty(Fin) Then
                sql += Fin
            End If
            Fin = String.Empty
            OperadorLogico = " And ("
        End If


        If Not TodosEmisor Then


            For Each Item In Col_Emisor
                Dim NombreItem As String = Item.ToString.Trim
                Dim ConsultaExtra As String = " " + OperadorLogico + " CodEmis like '%" + NombreItem + "%'"
                sql += ConsultaExtra
                OperadorLogico = " Or"
                Fin = ")"
            Next







            If Not String.IsNullOrEmpty(Fin) Then
                sql += Fin
            End If
            Fin = String.Empty
            OperadorLogico = " And ("
        End If

        If Not TodosEstDocumento Then



            For Each Item In Col_EstDocumento
                Dim NombreItem As String = Item.ToString.Trim
                Dim ConsultaExtra As String = " " + OperadorLogico + " DocEst like '%" + NombreItem + "%'"
                sql += ConsultaExtra
                OperadorLogico = " Or"
                Fin = ")"
            Next
            If Not String.IsNullOrEmpty(Fin) Then
                sql += Fin
            End If
            Fin = String.Empty
            OperadorLogico = " And ("

        End If




        If Not TodosInstrumento Then


            For Each Item In Col_Instrumento
                Dim NombreItem As String = Item.ToString.Trim
                Dim ConsultaExtra As String = " " + OperadorLogico + " CodIF  like '%" + NombreItem + "%'"
                sql += ConsultaExtra
                OperadorLogico = " Or"
                Fin = ")"
            Next
            If Not String.IsNullOrEmpty(Fin) Then
                sql += Fin
            End If
            Fin = String.Empty
            OperadorLogico = " And ("

        End If
        If Not TodosCalRiesgo Then


            For Each Item In Col_CalRiesgo
                Dim NombreItem As String = Item.ToString.Trim
                Dim ConsultaExtra As String = " " + OperadorLogico + " CodCalRi   like '%" + NombreItem + "%'"
                sql += ConsultaExtra
                OperadorLogico = " Or"
                Fin = ")"
            Next
            If Not String.IsNullOrEmpty(Fin) Then
                sql += Fin
            End If
            Fin = String.Empty
            OperadorLogico = " And ("
        End If

        If Not TodosTipOperacion Then


            For Each Item In Col_TipOperacion
                Dim NombreItem As String = Item.ToString.Trim
                Dim ConsultaExtra As String = "" + OperadorLogico + " TipoOper   like '%" + NombreItem + "%'"
                sql += ConsultaExtra
                OperadorLogico = " Or"
                Fin = ")"
            Next
            If Not String.IsNullOrEmpty(Fin) Then
                sql += Fin
            End If
            Fin = String.Empty
            OperadorLogico = " And ("
        End If


        If Not TodosOriFond Then


            For Each Item In Col_OriFond
                Dim NombreItem As String = Item.ToString.Trim
                Dim ConsultaExtra As String = " " + OperadorLogico + " OFon  like '%" + NombreItem + "%'"
                sql += ConsultaExtra
                OperadorLogico = " Or"
                Fin = ")"
            Next
            If Not String.IsNullOrEmpty(Fin) Then
                sql += Fin
            End If
            Fin = String.Empty
            OperadorLogico = " And ("
        End If

        If Not TodosCalERiesgo Then


            For Each Item In Col_CalERiesgo
                Dim NombreItem As String = Item.ToString.Trim
                Dim ConsultaExtra As String = " " + OperadorLogico + " CodECalRi  like '%" + NombreItem + "%'"
                sql += ConsultaExtra
                OperadorLogico = " Or"
                Fin = ")"
            Next
            If Not String.IsNullOrEmpty(Fin) Then
                sql += Fin
            End If
            Fin = String.Empty
            OperadorLogico = " And ("
        End If

        If Not TodosAut Then



            For Each Item In Col_Aut
                Dim NombreItem As String = Item.ToString.Trim
                Dim ConsultaExtra As String = " " + OperadorLogico + " AutEstado  like '%" + NombreItem + "%'"
                sql += ConsultaExtra
                OperadorLogico = " Or"
                Fin = ")"
            Next
            If Not String.IsNullOrEmpty(Fin) Then
                sql += Fin
            End If
            Fin = String.Empty
            OperadorLogico = " And ("
        End If


        If Not TodosPlazo Then


            For Each Item In Col_Plazo
                Dim NombreItem As String = Item.ToString.Trim
                Dim ConsultaExtra As String = " " + OperadorLogico + " PlazoFact   like '%" + NombreItem + "%'"
                sql += ConsultaExtra
                OperadorLogico = " Or"
                Fin = ")"
            Next
            If Not String.IsNullOrEmpty(Fin) Then
                sql += Fin
            End If
            Fin = String.Empty
            OperadorLogico = " And ("
        End If
        If Not TodosPais Then



            For Each Item In Col_Pais
                Dim NombreItem As String = Item.ToString.Trim
                Dim ConsultaExtra As String = " " + OperadorLogico + " CodPais   like '%" + NombreItem + "%'"
                sql += ConsultaExtra
                OperadorLogico = " Or"
                Fin = ")"
            Next
            If Not String.IsNullOrEmpty(Fin) Then
                sql += Fin
            End If
            Fin = String.Empty
            OperadorLogico = " And ("
        End If
        If Not TodosTipMercado Then



            For Each Item In Col_TipMercado
                Dim NombreItem As String = Item.ToString.Trim
                Dim ConsultaExtra As String = " " + OperadorLogico + " TipoMerc   like '%" + NombreItem + "%'"
                sql += ConsultaExtra
                OperadorLogico = " Or"
                Fin = ")"
            Next

            If Not String.IsNullOrEmpty(Fin) Then
                sql += Fin
            End If
            Fin = String.Empty
        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
        da = New SqlDataAdapter(sql, cn)
        da.SelectCommand.CommandType = CommandType.Text

        da.Fill(ds, "Resultado")
        cn.Close()
        Return ds


    End Function
    Public Function CargarRegistro(ByVal IdRegistro As Integer) As InversionesCE
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim oInversion As InversionesCE
        Dim sql As String
        Dim cmd As SqlCommand
        Dim dr As SqlDataReader

        sql = "SELECT * FROM [dbo].[INV0] WHERE [DocId]=@DocId"
        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
        cmd = New SqlCommand(sql, cn)
        cmd.Parameters.Add("@DocId", SqlDbType.Int).Value = IdRegistro

        dr = cmd.ExecuteReader

        If dr.HasRows Then
            oInversion = New InversionesCE
            While dr.Read
                oInversion.CodigoEmpresa = dr.Item("CodEmpr").ToString
                oInversion.CodigoInstrumentoFinanciero = dr.Item("CodIF").ToString
                If (String.IsNullOrEmpty(dr.Item("CodEmis").ToString)) Then
                    oInversion.CodigoEmisor = -1
                Else
                    oInversion.CodigoEmisor = dr.Item("CodEmis").ToString
                End If

                oInversion.CodigoCalificacionDeRiesgo = dr.Item("CodCalRi").ToString
                oInversion.CodigoEmpresaCalificadora = dr.Item("CodECalRi").ToString
                oInversion.Pais = dr.Item("CodPais").ToString
                oInversion.TipoMercado = dr.Item("TipoMerc").ToString
                oInversion.CodigoIversion = dr.Item("CodInv").ToString
                oInversion.EstadoDocumento = dr.Item("DocEst").ToString
                oInversion.NombreManual = dr.Item("NombreManIF").ToString
                oInversion.CodigoManual = dr.Item("CodManIF").ToString
                oInversion.Periodicidad = dr.Item("Periodicidad").ToString
                oInversion.TipoTasa = dr.Item("TipTasa").ToString
                oInversion.TipoRenta = dr.Item("TipRenta").ToString


                If String.IsNullOrEmpty(dr.Item("Plazo").ToString) Then
                    oInversion.PlazoNumero = 0
                Else
                    oInversion.PlazoNumero = dr.Item("Plazo")
                End If

                oInversion.PlazoFactor = dr.Item("PlazoFact").ToString
                oInversion.Comentarios = dr.Item("Coment").ToString
                oInversion.TipoOperacion = dr.Item("TipoOper").ToString
                oInversion.OFondo = dr.Item("OFon").ToString
                oInversion.EstadoProceso = dr.Item("EstadoProceso").ToString
                oInversion.FechaDocumento = Operaciones.ConvertirFecha(dr.Item("FechaDoc").ToString)
            End While
            dr.Close()
            cmd.Dispose()


            oInversion.TipoInstrumentoFinanciero = Me.ObtenerTipoInstrumentoFinanciero(oInversion.CodigoInstrumentoFinanciero)

        Else

        End If


        'oSqlConnection.Dispose()

        cn.Close()
        Return oInversion
    End Function




    Public Function CargarRegistroPropuesta(ByVal IdRegistro As Integer) As InversionesCE
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        Dim oInversion As InversionesCE
        Dim sql As String
        Dim cmd As SqlCommand
        Dim dr As SqlDataReader

        sql = "SELECT * FROM [dbo].[PIN0] WHERE [DocId]=@DocId"
        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
        cmd = New SqlCommand(sql, cn)
        cmd.Parameters.Add("@DocId", SqlDbType.Int).Value = IdRegistro

        dr = cmd.ExecuteReader

        If dr.HasRows Then
            oInversion = New InversionesCE
            While dr.Read
                'oInversion.CodigoEmpresa = dr.Item("CodEmpr")
                'oInversion.CodigoInstrumentoFinanciero = dr.Item("CodIF")
                oInversion.CodigoEmisor = dr.Item("CodEmis")
                'oInversion.CodigoCalificacionDeRiesgo = dr.Item("CodCalRi").ToString
                'oInversion.CodigoEmpresaCalificadora = dr.Item("CodECalRi").ToString
                'oInversion.Pais = dr.Item("CodPais").ToString
                'oInversion.TipoMercado = dr.Item("TipoMerc").ToString
                'oInversion.CodigoIversion = dr.Item("CodInv").ToString
                'oInversion.EstadoDocumento = dr.Item("DocEst").ToString
                'oInversion.NombreManual = dr.Item("NombreManIF").ToString
                'oInversion.CodigoManual = dr.Item("CodManIF").ToString
                'oInversion.Periodicidad = dr.Item("Periodicidad").ToString
                'oInversion.TipoTasa = dr.Item("TipTasa").ToString
                'oInversion.TipoRenta = dr.Item("TipRenta").ToString
                'oInversion.PlazoNumero = dr.Item("Plazo").ToString
                'oInversion.PlazoFactor = dr.Item("PlazoFact").ToString
                'oInversion.Comentarios = dr.Item("Coment").ToString


            End While
            dr.Close()
            cmd.Dispose()


            '    oInversion.TipoInstrumentoFinanciero = Me.ObtenerTipoInstrumentoFinanciero(oInversion.CodigoInstrumentoFinanciero)

        Else

        End If


        'oSqlConnection.Dispose()

        cn.Close()
        Return oInversion
    End Function





End Class