Inversiones/DAO/BonosDAO.vb

Imports System.Data.SqlClient
Public Class BonosDAO


    Dim Operaciones As New Operaciones
    Dim oDAOGeneral As New DAOGeneral

    Public Function NuevoIngreso(ByVal Coleccion As Collection, ByVal Codigo As String, ByVal InstrumentoFinanciero As String)
        Dim objCon As New Conexion
        Dim retorno As Integer
        Dim sql As String
        Dim cmd As SqlCommand
        Dim Tabla As String
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If
        If (InstrumentoFinanciero = "CINV") Then
            Tabla = "CIN1"
        ElseIf (InstrumentoFinanciero = "BONO") Then
            Tabla = "BON1"
        ElseIf (InstrumentoFinanciero = "EURB") Then
            Tabla = "EURB1"
        ElseIf (InstrumentoFinanciero = "TIT") Then
            Tabla = "TIT0_0"
        Else
            Return 0

        End If





        sql = "INSERT INTO " + Tabla + "(
           [CodInv]
           ,[NumCupon]
           ,[FechaCupon]
           ,[Dias]
           ,[Ingreso]
           ,[PorcImpuesto]
           ,[MontoImpuesto]
           ,[Liquido]
) VALUES(
           @CodInv
           ,@NumCupon
           ,@FechaCupon
           ,@Dias
           ,@Ingreso
           ,@PorcImpuesto
           ,@MontoImpuesto
           ,@Liquido
)
    "

        cmd = New SqlCommand
        cmd.CommandText = sql

        Try



            With cmd.Parameters
                .Add("@CodInv", SqlDbType.VarChar).Value = Codigo
                .Add("@NumCupon", SqlDbType.VarChar).Value = Operaciones.ConvertirEntero(Coleccion(1).ToString)
                .Add("@FechaCupon", SqlDbType.Date).Value = Operaciones.ConvertirFecha(Coleccion(2).ToString)
                .Add("@Dias", SqlDbType.Int).Value = Operaciones.ConvertirEntero(Coleccion(3).ToString)
                .Add("@Ingreso", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(4).ToString)
                .Add("@PorcImpuesto", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(5).ToString)
                .Add("@MontoImpuesto", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(6).ToString)
                .Add("@Liquido", SqlDbType.Float).Value = Coleccion(7).ToString
            End With


            cmd.Connection = cn
            retorno = cmd.ExecuteNonQuery

        Catch ex As Exception
            MsgBox("Datos de ingreso no guardados")
            Return Nothing
        End Try
        cn.Close()
        Return retorno

    End Function















    Public Sub EliminarImpuesto(ByVal Codigo 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
        sql = "DELETE FROM [dbo].[IMP]
      WHERE [CodInv]=@CodInv"


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

        res = cmd.ExecuteNonQuery
        cn.Close()
    End Sub



    Public Sub EliminarIngreso(ByVal Codigo As String, ByVal NoCupon As Integer, ByVal CodigoInstrumento As String)
        Dim objCon As New Conexion
        Dim sql As String
        Dim cmd As SqlCommand
        Dim res As Integer
        Dim Tabla As String = String.Empty
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If
        If (CodigoInstrumento = "CINV") Then
            Tabla = "CIN1"
        ElseIf (CodigoInstrumento = "BONO") Then
            Tabla = "BON1"

        ElseIf (CodigoInstrumento = "TIT") Then
            Tabla = "TIT0_0"

        ElseIf (CodigoInstrumento = "EURB") Then
            Tabla = "EURB1"
        End If

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


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

        res = cmd.ExecuteNonQuery
        cn.Close()
    End Sub

    Public Sub EliminarIngresoCompleto(ByVal Codigo As String, ByVal CodigoInstrumento As String)
        Dim objCon As New Conexion
        Dim sql As String
        Dim cmd As SqlCommand
        Dim res As Integer
        Dim Tabla As String = String.Empty
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If
        If (CodigoInstrumento = "CINV") Then
            Tabla = "CIN1"
        ElseIf (CodigoInstrumento = "BONO") Then
            Tabla = "BON1"
        End If

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

        Try


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

            res = cmd.ExecuteNonQuery
        Catch ex As Exception
            MsgBox("Datos no actualizados")
        End Try
        cn.Close()
    End Sub





    Public Function CargarIngreso(ByVal codigo As String, ByVal CodigoInstrumento As String)
        Dim objCon As New Conexion
        Dim Tabla As String
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If
        If (CodigoInstrumento = "CINV") Then
            Tabla = "CIN1"
        ElseIf (CodigoInstrumento = "BONO") Then
            Tabla = "BON1"
        ElseIf (CodigoInstrumento = "EURB") Then
            Tabla = "EURB1"
        ElseIf (CodigoInstrumento = "TIT") Then
            Tabla = "TIT0_0"
        Else
            Return 0
        End If

        Dim sql = "select * from " + Tabla + " where CodInv='" + codigo + "' order by NumCupon"


        Dim cmd As New SqlCommand(sql, cn)
        Dim dr As SqlDataReader
        dr = cmd.ExecuteReader

        Return dr
    End Function


End Class