Inversiones/DAO/LETESDAO.vb

Imports System.Data.SqlClient
Public Class LETESDAO



    Public Function Nuevo(ByVal objLete As LETESCE) As Integer
        Dim retorno As Integer


        Return retorno
    End Function
    Public Function Nuevo(ByVal coleccion As Collection)
        Dim objCon As New Conexion
        Dim retorno As Integer = -1
        Dim query As String

        Dim Tabla As String = ""
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If





        Dim cmd As SqlCommand




        query = "INSERT INTO [dbo].[LET0]
           (
           [ValNom]
           ,[FOper]
           ,[FLiq]
           ,[RendBruto]
           ,[Plazo])
     VALUES
           (@ValNom
           ,@FOper
           ,@FLiq
           ,@RendBruto
           ,@Plazo
		   )"

        cmd = New SqlCommand
        cmd.CommandText = query

        With cmd.Parameters
            .Add("@ValNom", SqlDbType.Decimal).Value = coleccion(1)
            .Add("@FOper", SqlDbType.DateTime).Value = coleccion(2)
            .Add("@FLiq", SqlDbType.DateTime).Value = coleccion(3)

            .Add("@RendBruto", SqlDbType.Decimal).Value = coleccion(4)
            .Add("@Plazo", SqlDbType.Int).Value = coleccion(5)
        End With

        cmd.Connection = cn

        retorno = cmd.ExecuteNonQuery
        cn.Close()
        Return retorno
    End Function


    Public Function ActualizarLETE(ByVal coleccion As Collection, ByVal Instrumento As String)
        Dim objCon As New Conexion
        Dim retorno As Integer
        Dim sql As String
        Dim cmd As SqlCommand
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If
        If (Instrumento = "LETE") Then
            sql = "UPDATE [dbo].[LET0] SET 
            
           [ValNom]=@ValNom
           ,[FOper]=@FOper
           ,[FLiq]=@FLiq
           ,[RendBruto]=@RendBruto
           ,[Plazo]=@Plazo
           ,[ComisionCasa]=@ComisionCasa
           ,[ComisionBolsa]=@ComisionBolsa
           ,[CasasCorredoras]=@CasasCorredoras
           ,[Periodic]=@Periodic
           ,[AnioBase]=@AnioBase
          
            ,[ValTrans] = @ValTrans 
            ,[Precio] = @Precio
            ,[TotPagar]=  @TotPagar
            ,[RendNeto] = @RendNeto 
            ,[IngrBruto] =@IngrBruto
            ,[IngrNeto] =@IngrNeto
            ,[ComisionCasaValor] =@ComisionCasaValor
            ,[ComisionBolsaValor] =@ComisionBolsaValor
            ,[FUlt] =@FUlt
            ,[FRed] =@FRed
            ,[FVenc] =@FVenc

where [CodInv]=   @CodInv

            
"
        ElseIf (Instrumento = "PBUR") Then

            sql = "UPDATE [dbo].[PBUR] SET
       
           [ValNom]=@ValNom
           ,[FOper]=@FOper
           ,[FLiq]=@FLiq
           ,[RendBruto]=@RendBruto
           ,[Plazo]=@Plazo
           ,[ComisionCasa]=@ComisionCasa
           ,[ComisionBolsa]=@ComisionBolsa
           ,[CasasCorredoras]=@CasasCorredoras
           ,[Periodic]=@Periodic
           ,[AnioBase]=@AnioBase
           ,[ValorPar]= @ValorPar
           ,[FechaUltima]=@FechaUltima
           
            ,[ValTrans] = @ValTrans 
            ,[Precio] = @Precio
            ,[TotPagar]=  @TotPagar
            ,[RendNeto] = @RendNeto 
            ,[IngrBruto] =@IngrBruto
            ,[IngrNeto] =@IngrNeto
            ,[ComisionCasaValor] =@ComisionCasaValor
            ,[ComisionBolsaValor] =@ComisionBolsaValor
            ,[FUlt] =@FUlt
            ,[FRed] =@FRed
            ,[FVenc] =@FVenc
            ,[IntAcumulado]=@IntAcumulado
where [CodInv]=   @CodInv
            
"

        ElseIf (Instrumento = "VCN") Then

            sql = "UPDATE [dbo].[VCN] SET
           [ValNom]=@ValNom
           ,[FOper]=@FOper
           ,[FLiq]=@FLiq
           ,[RendBruto]=@RendBruto
           ,[Plazo]=@Plazo
           ,[ComisionCasa]=@ComisionCasa
           ,[ComisionBolsa]=@ComisionBolsa
           ,[CasasCorredoras]=@CasasCorredoras
           ,[Periodic]=@Periodic
           ,[AnioBase]=@AnioBase
           ,[ValorPar]= @ValorPar
           ,[FechaUltima]=@FechaUltima
           ,[OtrosCostos]=@OtrosCostos

            ,[ValTrans] = @ValTrans 
            ,[Precio] = @Precio
            ,[TotPagar]=  @TotPagar
            ,[RendNeto] = @RendNeto 
            ,[IngrBruto] =@IngrBruto
            ,[IngrNeto] =@IngrNeto
            ,[ComisionCasaValor] =@ComisionCasaValor
            ,[ComisionBolsaValor] =@ComisionBolsaValor
            ,[FUlt] =@FUlt
            ,[FRed] =@FRed
            ,[FVenc] =@FVenc
            ,[IntAcumulado]=@IntAcumulado
            where [CodInv]=   @CodInv
"



        ElseIf (Instrumento = "CETE") Then

            sql = "UPDATE [dbo].[CET0] SET
           [ValNom]=@ValNom
           ,[FOper]= @FOper
           ,[FLiq]=@FLiq
           ,[RendBruto]= @RendBruto
           ,[Plazo]=@Plazo
           ,[ComisionCasa]=  @ComisionCasa
           ,[ComisionBolsa]= @ComisionBolsa
           ,[CasasCorredoras]=     @CasasCorredoras
           ,[Periodic]=   @Periodic
           ,[AnioBase]=@AnioBase
           ,[FechaUltima]=@FechaUltima

            ,[ValTrans] = @ValTrans 
            ,[Precio] = @Precio
            ,[TotPagar]=  @TotPagar
            ,[RendNeto] = @RendNeto 
            ,[IngrBruto] =@IngrBruto
            ,[IngrNeto] =@IngrNeto
            ,[ComisionCasaValor] =@ComisionCasaValor
            ,[ComisionBolsaValor] =@ComisionBolsaValor
            ,[FUlt] =@FUlt
            ,[FRed] =@FRed
            ,[FVenc] =@FVenc
            ,[IntAcumulado]=@IntAcumulado
            where [CodInv]=   @CodInv
           
"
        End If

        'Try


        cmd = New SqlCommand
            cmd.CommandText = sql

            With cmd.Parameters
                .Add("@ValNom", SqlDbType.Decimal).Value = coleccion(1)

                .Add("@FOper", SqlDbType.DateTime).Value = coleccion(2)
                .Add("@FLiq", SqlDbType.DateTime).Value = coleccion(3)
                .Add("@RendBruto", SqlDbType.Decimal).Value = coleccion(4)
                .Add("@Plazo", SqlDbType.Int).Value = CInt(coleccion(5))
                .Add("@CodInv", SqlDbType.VarChar).Value = coleccion(6)
                .Add("@ComisionCasa", SqlDbType.Decimal).Value = CDec(coleccion(7))
                .Add("@ComisionBolsa", SqlDbType.Decimal).Value = CDec(coleccion(8))
                .Add("@Periodic", SqlDbType.VarChar).Value = coleccion(9)

                .Add("@AnioBase", SqlDbType.Int).Value = CInt(coleccion(10))

                .Add("@CasasCorredoras", SqlDbType.VarChar).Value = coleccion(11)

                If Instrumento = "LETE" Then
                    .Add("ValTrans", SqlDbType.Float).Value = coleccion(12)
                    .Add("Precio", SqlDbType.Float).Value = coleccion(13)
                    .Add("TotPagar", SqlDbType.Float).Value = coleccion(14)
                    .Add("RendNeto", SqlDbType.Float).Value = coleccion(15)
                    .Add("IngrBruto", SqlDbType.Float).Value = coleccion(16)
                    .Add("IngrNeto", SqlDbType.Float).Value = coleccion(17)
                    .Add("ComisionCasaValor", SqlDbType.Float).Value = coleccion(18)
                    .Add("ComisionBolsaValor", SqlDbType.Float).Value = coleccion(19)
                    .Add("FUlt", SqlDbType.Date).Value = coleccion(20)
                    .Add("FRed", SqlDbType.Date).Value = coleccion(21)
                    .Add("FVenc", SqlDbType.Date).Value = coleccion(22)
                End If

                If (Instrumento = "VCN" Or Instrumento = "PBUR") Then


                    .Add("@ValorPar", SqlDbType.Bit).Value = CByte(coleccion(12))
                    .Add("@FechaUltima", SqlDbType.DateTime).Value = CDate(coleccion(13))



                ElseIf Instrumento = "CETE" Then
                    .Add("@FechaUltima", SqlDbType.DateTime).Value = CDate(coleccion(12))
                    .Add("ValTrans", SqlDbType.Float).Value = coleccion(13)
                    .Add("Precio", SqlDbType.Float).Value = coleccion(14)
                    .Add("TotPagar", SqlDbType.Float).Value = coleccion(15)
                    .Add("RendNeto", SqlDbType.Float).Value = coleccion(16)
                    .Add("IngrBruto", SqlDbType.Float).Value = coleccion(17)
                    .Add("IngrNeto", SqlDbType.Float).Value = coleccion(18)
                    .Add("ComisionCasaValor", SqlDbType.Float).Value = coleccion(19)
                    .Add("ComisionBolsaValor", SqlDbType.Float).Value = coleccion(20)
                    .Add("FUlt", SqlDbType.Date).Value = coleccion(21)
                    .Add("FRed", SqlDbType.Date).Value = coleccion(22)
                .Add("FVenc", SqlDbType.Date).Value = coleccion(23)
                .Add("@IntAcumulado", SqlDbType.Float).Value = coleccion(24)
            End If
                If Instrumento = "VCN" Then
                    .Add("@OtrosCostos", SqlDbType.Decimal).Value = CDec(coleccion(14))
                    .Add("ValTrans", SqlDbType.Float).Value = coleccion(15)
                    .Add("Precio", SqlDbType.Float).Value = coleccion(16)
                    .Add("TotPagar", SqlDbType.Float).Value = coleccion(17)
                    .Add("RendNeto", SqlDbType.Float).Value = coleccion(18)
                    .Add("IngrBruto", SqlDbType.Float).Value = coleccion(19)
                    .Add("IngrNeto", SqlDbType.Float).Value = coleccion(20)
                    .Add("ComisionCasaValor", SqlDbType.Float).Value = coleccion(21)
                    .Add("ComisionBolsaValor", SqlDbType.Float).Value = coleccion(22)
                    .Add("FUlt", SqlDbType.Date).Value = coleccion(23)
                    .Add("FRed", SqlDbType.Date).Value = coleccion(24)
                .Add("FVenc", SqlDbType.Date).Value = coleccion(25)
                .Add("@IntAcumulado", SqlDbType.Float).Value = coleccion(26)
            End If
                If Instrumento = "PBUR" Then
                    .Add("ValTrans", SqlDbType.Float).Value = coleccion(14)
                    .Add("Precio", SqlDbType.Float).Value = coleccion(15)
                    .Add("TotPagar", SqlDbType.Float).Value = coleccion(16)
                    .Add("RendNeto", SqlDbType.Float).Value = coleccion(17)
                    .Add("IngrBruto", SqlDbType.Float).Value = coleccion(18)
                    .Add("IngrNeto", SqlDbType.Float).Value = coleccion(19)
                    .Add("ComisionCasaValor", SqlDbType.Float).Value = coleccion(20)
                    .Add("ComisionBolsaValor", SqlDbType.Float).Value = coleccion(21)
                    .Add("FUlt", SqlDbType.Date).Value = coleccion(22)
                    .Add("FRed", SqlDbType.Date).Value = coleccion(23)
                .Add("FVenc", SqlDbType.Date).Value = coleccion(24)
                .Add("@IntAcumulado", SqlDbType.Float).Value = coleccion(25)
            End If

            End With


            cmd.Connection = cn

            retorno = cmd.ExecuteNonQuery
            MsgBox("Registro Modificado con éxito")
        'Catch ex As Exception
        '    MsgBox("Error al modificar el registro")
        'End Try
        cn.Close()
        Return retorno
    End Function

    Public Function NuevoIngreso(ByVal coleccion As Collection, ByVal Instrumento As String)
        Dim objCon As New Conexion
        Dim retorno As Integer
        Dim sql As String
        Dim cmd As SqlCommand
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If
        If (Instrumento = "LETE") Then
            sql = "INSERT INTO [dbo].[LET0](
            [CodInv]
           ,[ValNom]
           ,[FOper]
           ,[FLiq]
           ,[RendBruto]
           ,[Plazo]
           ,[ComisionCasa]
           ,[ComisionBolsa]
           ,[CasasCorredoras]
           ,[Periodic]
           ,[AnioBase]
            ,[ValTrans] 
            ,[Precio]
            ,[TotPagar] 
            ,[RendNeto] 
            ,[IngrBruto] 
            ,[IngrNeto] 
            ,[ComisionCasaValor] 
            ,[ComisionBolsaValor] 
            ,[FUlt] 
            ,[FRed] 
            ,[FVenc]         

)
           VALUES(
            @CodInv 
           ,@ValNom
           ,@FOper
           ,@FLiq
           ,@RendBruto
           ,@Plazo
           ,@ComisionCasa
           ,@ComisionBolsa
           ,@CasasCorredoras
           ,@Periodic 
           ,@AnioBase 

            ,@ValTrans 
            ,@Precio 
            ,@TotPagar 
            ,@RendNeto 
            ,@IngrBruto 
            ,@IngrNeto 
            ,@ComisionCasaValor 
            ,@ComisionBolsaValor 
            ,@FUlt 
            ,@FRed 
            ,@FVenc    

)
            
"
        ElseIf (Instrumento = "PBUR") Then

            sql = "INSERT INTO [dbo].[PBUR](
            [CodInv]
           ,[ValNom]
           ,[FOper]
           ,[FLiq]
           ,[RendBruto]
           ,[Plazo]
           ,[ComisionCasa]
           ,[ComisionBolsa]
           ,[CasasCorredoras]
           ,[Periodic]
           ,[AnioBase]
           ,[ValorPar]
           ,[FechaUltima]

,[ValTrans] 
            ,[Precio]
            ,[TotPagar] 
            ,[RendNeto] 
            ,[IngrBruto] 
            ,[IngrNeto] 
            ,[ComisionCasaValor] 
            ,[ComisionBolsaValor] 
            ,[FUlt] 
            ,[FRed] 
            ,[FVenc]  
            ,[IntAcumulado]

)
           VALUES(
            @CodInv 
           ,@ValNom
           ,@FOper
           ,@FLiq
           ,@RendBruto
           ,@Plazo
           ,@ComisionCasa
           ,@ComisionBolsa
           ,@CasasCorredoras
           ,@Periodic 
           ,@AnioBase
           ,@ValorPar
           ,@FechaUltima
            ,@ValTrans 
            ,@Precio 
            ,@TotPagar 
            ,@RendNeto 
            ,@IngrBruto 
            ,@IngrNeto 
            ,@ComisionCasaValor 
            ,@ComisionBolsaValor 
            ,@FUlt 
            ,@FRed 
            ,@FVenc    
            ,@IntAcumulado
)
            
"

        ElseIf (Instrumento = "VCN") Then

            sql = "INSERT INTO [dbo].[VCN](
            [CodInv]
           ,[ValNom]
           ,[FOper]
           ,[FLiq]
           ,[RendBruto]
           ,[Plazo]
           ,[ComisionCasa]
           ,[ComisionBolsa]
           ,[CasasCorredoras]
           ,[Periodic]
           ,[AnioBase]
           ,[ValorPar]
           ,[FechaUltima]
           ,[OtrosCostos]

,[ValTrans] 
            ,[Precio]
            ,[TotPagar] 
            ,[RendNeto] 
            ,[IngrBruto] 
            ,[IngrNeto] 
            ,[ComisionCasaValor] 
            ,[ComisionBolsaValor] 
            ,[FUlt] 
            ,[FRed] 
            ,[FVenc]    
            ,[IntAcumulado]

)
           VALUES(
            @CodInv 
           ,@ValNom
           ,@FOper
           ,@FLiq
           ,@RendBruto
           ,@Plazo
           ,@ComisionCasa
           ,@ComisionBolsa
           ,@CasasCorredoras
           ,@Periodic 
           ,@AnioBase 
           ,@ValorPar
           ,@FechaUltima
           ,@OtrosCostos
            ,@ValTrans 
            ,@Precio 
            ,@TotPagar 
            ,@RendNeto 
            ,@IngrBruto 
            ,@IngrNeto 
            ,@ComisionCasaValor 
            ,@ComisionBolsaValor 
            ,@FUlt 
            ,@FRed 
            ,@FVenc  
            ,@IntAcumulado
)
            
"



        ElseIf (Instrumento = "CETE") Then

            sql = "INSERT INTO [dbo].[CET0](
            [CodInv]
           ,[ValNom]
           ,[FOper]
           ,[FLiq]
           ,[RendBruto]
           ,[Plazo]
           ,[ComisionCasa]
           ,[ComisionBolsa]
           ,[CasasCorredoras]
           ,[Periodic]
           ,[AnioBase]
           ,[FechaUltima]

,[ValTrans] 
            ,[Precio]
            ,[TotPagar] 
            ,[RendNeto] 
            ,[IngrBruto] 
            ,[IngrNeto] 
            ,[ComisionCasaValor] 
            ,[ComisionBolsaValor] 
            ,[FUlt] 
            ,[FRed] 
            ,[FVenc]    
            ,[IntAcumulado]

)
           VALUES(
            @CodInv 
           ,@ValNom
           ,@FOper
           ,@FLiq
           ,@RendBruto
           ,@Plazo
           ,@ComisionCasa
           ,@ComisionBolsa
           ,@CasasCorredoras
           ,@Periodic 
           ,@AnioBase 
           ,@FechaUltima
            ,@ValTrans 
            ,@Precio 
            ,@TotPagar 
            ,@RendNeto 
            ,@IngrBruto 
            ,@IngrNeto 
            ,@ComisionCasaValor 
            ,@ComisionBolsaValor 
            ,@FUlt 
            ,@FRed 
            ,@FVenc
            ,@IntAcumulado
)
            
"
        End If

        Try


            cmd = New SqlCommand
            cmd.CommandText = sql

            With cmd.Parameters
                .Add("@ValNom", SqlDbType.Decimal).Value = coleccion(1)

                .Add("@FOper", SqlDbType.DateTime).Value = coleccion(2)
                .Add("@FLiq", SqlDbType.DateTime).Value = coleccion(3)
                .Add("@RendBruto", SqlDbType.Decimal).Value = coleccion(4)
                .Add("@Plazo", SqlDbType.Int).Value = CInt(coleccion(5))
                .Add("@CodInv", SqlDbType.VarChar).Value = coleccion(6)
                .Add("@ComisionCasa", SqlDbType.Decimal).Value = CDec(coleccion(7))
                .Add("@ComisionBolsa", SqlDbType.Decimal).Value = CDec(coleccion(8))
                .Add("@Periodic", SqlDbType.VarChar).Value = coleccion(9)

                .Add("@AnioBase", SqlDbType.Int).Value = CInt(coleccion(10))

                .Add("@CasasCorredoras", SqlDbType.VarChar).Value = coleccion(11)

                If Instrumento = "LETE" Then
                    .Add("ValTrans", SqlDbType.Float).Value = coleccion(12)
                    .Add("Precio", SqlDbType.Float).Value = coleccion(13)
                    .Add("TotPagar", SqlDbType.Float).Value = coleccion(14)
                    .Add("RendNeto", SqlDbType.Float).Value = coleccion(15)
                    .Add("IngrBruto", SqlDbType.Float).Value = coleccion(16)
                    .Add("IngrNeto", SqlDbType.Float).Value = coleccion(17)
                    .Add("ComisionCasaValor", SqlDbType.Float).Value = coleccion(18)
                    .Add("ComisionBolsaValor", SqlDbType.Float).Value = coleccion(19)
                    .Add("FUlt", SqlDbType.Date).Value = coleccion(20)
                    .Add("FRed", SqlDbType.Date).Value = coleccion(21)
                    .Add("FVenc", SqlDbType.Date).Value = coleccion(22)
                End If

                If (Instrumento = "VCN" Or Instrumento = "PBUR") Then


                    .Add("@ValorPar", SqlDbType.Bit).Value = CByte(coleccion(12))
                    .Add("@FechaUltima", SqlDbType.DateTime).Value = CDate(coleccion(13))



                ElseIf Instrumento = "CETE" Then
                    .Add("@FechaUltima", SqlDbType.DateTime).Value = CDate(coleccion(12))
                    .Add("ValTrans", SqlDbType.Float).Value = coleccion(13)
                    .Add("Precio", SqlDbType.Float).Value = coleccion(14)
                    .Add("TotPagar", SqlDbType.Float).Value = coleccion(15)
                    .Add("RendNeto", SqlDbType.Float).Value = coleccion(16)
                    .Add("IngrBruto", SqlDbType.Float).Value = coleccion(17)
                    .Add("IngrNeto", SqlDbType.Float).Value = coleccion(18)
                    .Add("ComisionCasaValor", SqlDbType.Float).Value = coleccion(19)
                    .Add("ComisionBolsaValor", SqlDbType.Float).Value = coleccion(20)
                    .Add("FUlt", SqlDbType.Date).Value = coleccion(21)
                    .Add("FRed", SqlDbType.Date).Value = coleccion(22)
                    .Add("FVenc", SqlDbType.Date).Value = coleccion(23)
                    .Add("@IntAcumulado", SqlDbType.Float).Value = coleccion(24)
                End If
                If Instrumento = "VCN" Then
                    .Add("@OtrosCostos", SqlDbType.Decimal).Value = CDec(coleccion(14))
                    .Add("ValTrans", SqlDbType.Float).Value = coleccion(15)
                    .Add("Precio", SqlDbType.Float).Value = coleccion(16)
                    .Add("TotPagar", SqlDbType.Float).Value = coleccion(17)
                    .Add("RendNeto", SqlDbType.Float).Value = coleccion(18)
                    .Add("IngrBruto", SqlDbType.Float).Value = coleccion(19)
                    .Add("IngrNeto", SqlDbType.Float).Value = coleccion(20)
                    .Add("ComisionCasaValor", SqlDbType.Float).Value = coleccion(21)
                    .Add("ComisionBolsaValor", SqlDbType.Float).Value = coleccion(22)
                    .Add("FUlt", SqlDbType.Date).Value = coleccion(23)
                    .Add("FRed", SqlDbType.Date).Value = coleccion(24)
                    .Add("FVenc", SqlDbType.Date).Value = coleccion(25)
                    .Add("@IntAcumulado", SqlDbType.Float).Value = coleccion(26)
                End If
                If Instrumento = "PBUR" Then
                    .Add("ValTrans", SqlDbType.Float).Value = coleccion(14)
                    .Add("Precio", SqlDbType.Float).Value = coleccion(15)
                    .Add("TotPagar", SqlDbType.Float).Value = coleccion(16)
                    .Add("RendNeto", SqlDbType.Float).Value = coleccion(17)
                    .Add("IngrBruto", SqlDbType.Float).Value = coleccion(18)
                    .Add("IngrNeto", SqlDbType.Float).Value = coleccion(19)
                    .Add("ComisionCasaValor", SqlDbType.Float).Value = coleccion(20)
                    .Add("ComisionBolsaValor", SqlDbType.Float).Value = coleccion(21)
                    .Add("FUlt", SqlDbType.Date).Value = coleccion(22)
                    .Add("FRed", SqlDbType.Date).Value = coleccion(23)
                    .Add("FVenc", SqlDbType.Date).Value = coleccion(24)
                    .Add("@IntAcumulado", SqlDbType.Float).Value = coleccion(25)
                End If

            End With


            cmd.Connection = cn

            retorno = cmd.ExecuteNonQuery
            MsgBox("Registro creado con éxito")
        Catch ex As Exception
            MsgBox("Error al crear el registro")
        End Try
        cn.Close()
        Return retorno
    End Function

    Public Sub Eliminar(ByVal Eliminar As String, ByVal Instrumento As String)
        Dim objCon As New Conexion
        Dim sql As String
        Dim cmd As SqlCommand
        Dim res As Integer
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If
        If (Instrumento = "LETE") Then
            sql = "DELETE FROM [dbo].[LET0]
      WHERE [CodInv]=@CodInv"
        ElseIf (Instrumento = "PBUR") Then
            sql = "DELETE FROM [dbo].[PBUR]
      WHERE [CodInv]=@CodInv"

        ElseIf (Instrumento = "VCN") Then
            sql = "DELETE FROM [dbo].[VCN]
      WHERE [CodInv]=@CodInv"

        ElseIf (Instrumento = "CETE") Then
            sql = "DELETE FROM [dbo].[CET0]
      WHERE [CodInv]=@CodInv"
        End If


        cn = objCon.Conectar
        Try




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

            res = cmd.ExecuteNonQuery
            MsgBox("Registro eliminado con éxito")
        Catch ex As Exception
            MsgBox("Error al eliminar el registro")
        End Try
        cn.Close()
    End Sub
    Public Function Nuevo(ByVal oLete As LETESCE, CodigoInversion As String) As Integer
        Dim Resultado As Integer = 0
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If
        If Not oLete Is Nothing Then
            Dim Transaccion As SqlTransaction
            Dim query As String

            Dim cmd As SqlCommand = cn.CreateCommand

            Transaccion = cn.BeginTransaction("NuevoLete")
            cmd.Connection = cn
            cmd.Transaction = Transaccion

            query = "INSERT INTO [dbo].[LET0]
           ([CodInv]
           ,[CodCasa]
           ,[ValNom]
           ,[ValTrans]
           ,[Precio]
           ,[Impuestos]
           ,[TotPagar]
           ,[FOper]
           ,[FLiq]
           ,[RendBruto]
           ,[RendNeto]
           ,[Periodic]
           ,[Plazo]
           ,[IngrBruto]
           ,[IngrNeto])
     VALUES
           (@CodInv
           ,@CodCasa
           ,@ValNom
           ,@ValTrans
           ,@Precio
           ,@Impuestos
           ,@TotPagar
           ,@FOper
           ,@FLiq
           ,@RendBruto
           ,@RendNeto
           ,@Periodic
           ,@Plazo
           ,@IngrBruto
           ,@IngrNeto
		   )"

            cmd.CommandText = query

            With cmd.Parameters
                '.Add("@DocId", SqlDbType.Int).Value = DocId
                .Add("@CodInv", SqlDbType.VarChar).Value = CodigoInversion
                .Add("@CodCasa", SqlDbType.VarChar).Value = oLete.CodigoCasa
                .Add("@ValNom", SqlDbType.Float).Value = oLete.ValorNominal
                .Add("@ValTrans", SqlDbType.Float).Value = oLete.ValorTransado
                .Add("@Precio", SqlDbType.Float).Value = oLete.Precio
                .Add("@Impuestos", SqlDbType.Float).Value = oLete.Impuestos
                .Add("@TotPagar", SqlDbType.Float).Value = oLete.TotalaPagar
                .Add("@FOper", SqlDbType.DateTime).Value = oLete.FechaOperacion
                .Add("@FLiq", SqlDbType.DateTime).Value = oLete.FechaLiquidacion
                .Add("@RendBruto", SqlDbType.Float).Value = oLete.RendimientoBruto
                .Add("@RendNeto", SqlDbType.Float).Value = oLete.RendimientoNeto
                .Add("@Periodic", SqlDbType.VarChar).Value = oLete.Periodicidad
                .Add("@Plazo", SqlDbType.Int).Value = oLete.Plazo
                .Add("@IngrBruto", SqlDbType.Float).Value = oLete.IngresoBruto
                .Add("@IngrNeto", SqlDbType.Float).Value = oLete.IngresoNeto
            End With

            Try
                Resultado = cmd.ExecuteNonQuery()
                Transaccion.Commit()

            Catch ex As Exception
                MsgBox("Error al Guardar Lete : " & ex.GetType.ToString)
                MsgBox("Mensaje: " & ex.Message)
                Resultado = 0
                Try
                    Transaccion.Rollback()
                Catch ex2 As Exception
                    MsgBox("Error en Rollback: " & ex2.GetType.ToString)
                    MsgBox("Mensaje Rollbak: " & ex2.Message)
                End Try
            End Try

            cmd.Dispose()
            cn.Dispose()
        Else
            Resultado = -1
        End If

        Return Resultado
    End Function


    Public Sub CargarRegistro(ByVal oLetes As LETESCE, Tipo As String, IdRegistro As Integer)
        Dim objCon As New Conexion
        Dim drd As SqlDataReader
        Dim cmd As SqlCommand
        Dim sql As String
        Dim Tabla As String
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If
        If Tipo = "P" Then
            Tabla = "PLET0"
        End If
        If Tipo = "I" Then
            Tabla = "ILET0"
        End If

        sql = "SELECT * FROM [dbo].[" & Tabla & "] T0 WHERE T0.[DocId]=@DocId"


        cmd = New SqlCommand
        cmd.CommandText = sql
        cmd.Parameters.Add("@DocId", SqlDbType.Int).Value = IdRegistro
        cmd.Connection = cn

        drd = cmd.ExecuteReader

        If drd.HasRows Then
            'SE ENCONTRO EL REGISTRO
            If drd.Read Then
                oLetes.ValorNominal = drd.Item("ValNom")
                oLetes.FechaOperacion = drd.Item("FOper")
                oLetes.FechaLiquidacion = drd.Item("FLiq")

                oLetes.Plazo = drd.Item("Plazo")
                oLetes.CodigoCasa = drd.Item("CodCasa")
                oLetes.Impuestos = drd.Item("Impuestos")
                oLetes.Periodicidad = drd.Item("Periodic")
                oLetes.RendimientoBruto = drd.Item("RendBruto")
                oLetes.RendimientoNeto = drd.Item("RendNeto")
                oLetes.IngresoBruto = drd.Item("IngrBruto")
                oLetes.IngresoNeto = drd.Item("IngrNeto")
                oLetes.ValorTransado = drd.Item("ValTrans")
                oLetes.Precio = drd.Item("Precio")
                oLetes.TotalaPagar = drd.Item("TotPagar")
            End If

        Else
            'NO SE ENCONTRO EL REGISTRO
        End If
        drd.Close()
        cmd.Dispose()
        cn.Dispose()

    End Sub
    Public Function CargarRegistro(ByVal CodInversion As String) As LETESCE
        Dim objCon As New Conexion
        Dim oLETES As LETESCE
        Dim cmd As SqlCommand
        Dim drd As SqlDataReader
        Dim vComprobar As Integer = 0
        Dim sql As String
        Dim vCodigoInversion As String = CodInversion
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If
        vComprobar = Comprobar(vCodigoInversion)
        If vComprobar > 0 Then
            oLETES = New LETESCE

            sql = "SELECT * FROM [dbo].[LET0] T0 WHERE T0.[CodInv]=@CodInv"

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

            drd = cmd.ExecuteReader
            If drd.HasRows Then
                'SE ENCONTRO EL REGISTRO
                If drd.Read Then
                    'oLETES.ValorNominal = drd.Item("ValNom").ToString
                    'oLETES.FechaOperacion = drd.Item("FOper").ToString
                    'oLETES.FechaLiquidacion = drd.Item("FLiq").ToString
                    'oLETES.Plazo = drd.Item("Plazo").ToString
                    'oLETES.CodigoCasa = drd.Item("CodCasa").ToString
                    'oLETES.Impuestos = drd.Item("Impuestos").ToString
                    'oLETES.Periodicidad = drd.Item("Periodic").ToString
                    'oLETES.RendimientoBruto = drd.Item("RendBruto").ToString
                    'oLETES.RendimientoNeto = drd.Item("RendNeto").ToString
                    'oLETES.IngresoBruto = drd.Item("IngrBruto").ToString
                    'oLETES.IngresoNeto = drd.Item("IngrNeto").ToString
                    'oLETES.ValorTransado = drd.Item("ValTrans").ToString
                    'oLETES.Precio = drd.Item("Precio").ToString
                    'oLETES.TotalaPagar = drd.Item("TotPagar").ToString
                End If

            Else
                'NO SE ENCONTRO EL REGISTRO
            End If
            drd.Close()
            cmd.Dispose()
            cn.Dispose()

        End If

        Return oLETES
    End Function
    Public Function Comprobar(ByVal CodInversion As String) As Integer
        Dim objCon As New Conexion
        Dim sql As String
        Dim cmd As SqlCommand
        Dim count As Integer = 0
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If
        sql = "SELECT COUNT('A') FROM [dbo].[LET0] T0 WHERE T0.CodInv=@CodInversion"
        cmd = New SqlCommand


        cmd.CommandText = sql
        cmd.Parameters.Add("@CodInversion", SqlDbType.VarChar).Value = CodInversion
        cmd.Connection = cn

        count = cmd.ExecuteScalar
        cn.Close()
        Return count
    End Function
    Public Function Cargar(ByVal CodInversion As String, ByVal Instrumento As String)
        Dim objCon As New Conexion
        Dim sql As String
        Dim cmd As SqlCommand
        Dim Coleccion As New Collection
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If
        If (Instrumento = "LETE") Then
            sql = "SELECT * FROM [dbo].[LET0] WHERE CodInv=@CodInversion"
        ElseIf (Instrumento = "PBUR") Then
            sql = "SELECT * FROM [dbo].[PBUR] WHERE CodInv=@CodInversion"
        ElseIf (Instrumento = "VCN") Then
            sql = "SELECT * FROM [dbo].[VCN] WHERE CodInv=@CodInversion"
        ElseIf (Instrumento = "CETE") Then
            sql = "SELECT * FROM [dbo].[CET0] WHERE CodInv=@CodInversion"
        End If
        cmd = New SqlCommand


        cmd.CommandText = sql
        cmd.Parameters.Add("@CodInversion", SqlDbType.VarChar).Value = CodInversion
        cmd.Connection = cn

        ' Dim Datos = cmd.ExecuteScalar
        Dim Datos = cmd.ExecuteReader
        If Datos.HasRows Then
            'SE ENCONTRO EL REGISTRO

            If Datos.Read Then


                Coleccion.Add(Datos.Item("ValNom"))
                Coleccion.Add(Datos.Item("FOper"))
                Coleccion.Add(Datos.Item("FLiq"))
                Coleccion.Add(Datos.Item("Plazo"))
                Coleccion.Add(Datos.Item("RendBruto"))
                Coleccion.Add(Datos.Item("CodCasa"))
                Coleccion.Add(Datos.Item("Periodic"))
                Coleccion.Add(Datos.Item("AnioBase"))
                Coleccion.Add(Datos.Item("ComisionCasa"))
                Coleccion.Add(Datos.Item("ComisionBolsa"))
                Coleccion.Add(Datos.Item("CodInv"))
                Coleccion.Add(Datos.Item("CasasCorredoras"))
                If (Instrumento = "PBUR" Or Instrumento = "VCN") Then
                    Coleccion.Add(Datos.Item("ValorPar"))
                    Coleccion.Add(Datos.Item("FechaUltima"))
                ElseIf Instrumento = "CETE" Then

                    Coleccion.Add(Datos.Item("FechaUltima"))
                End If

                If Instrumento = "VCN" Then
                    Coleccion.Add(Datos.Item("OtrosCostos"))
                End If
                cn.Close()
                Return Coleccion
            End If
        End If
        Coleccion.Add(False)
        cn.Close()
        Return Coleccion
    End Function
End Class