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