Inversiones/DAO/FondoInversionDAO.vb

Imports System.Data.SqlClient
Public Class FondoInversionDAO



    Dim Operaciones As New Operaciones


    Public Function Modificar(ByVal Coleccion As Collection, ByVal Codigo 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




        sql = "UPDATE [dbo].[FINV]
       SET 
           [CuoPart]=@CuoPart
           ,[VaPart]=@VaPart
           ,[CoCasa]=@CoCasa
           ,[CoBolsa]=@CoBolsa
           ,[DiasLiq]=@DiasLiq
           ,[FechaOpe]=@FechaOpe
           ,[FechaVen]=@FechaVen
           ,[Periodic]=@Periodic
           ,[Base]=@Base
           ,[PlaInv]=@PlaInv
           ,[RendOfre]=@RendOfre
           ,[Dividendo]=@Dividendo
   where [CodInv]=@CodInv  "





        cmd = New SqlCommand
        cmd.CommandText = sql

        With cmd.Parameters

            .Add("@CuoPart", SqlDbType.Float).Value = CDec(Coleccion(1).TrimEnd("%"))
            .Add("@VaPart", SqlDbType.Float).Value = CDec(Coleccion(2).TrimEnd("%"))
            .Add("@CoCasa", SqlDbType.Float).Value = CDec(Coleccion(3).TrimEnd("%"))
            .Add("@CoBolsa", SqlDbType.Float).Value = CDec(Coleccion(4).TrimEnd("%"))
            .Add("@DiasLiq", SqlDbType.Int).Value = CInt(Coleccion(5))
            .Add("@FechaOpe", SqlDbType.DateTime).Value = CDate(Coleccion(6))
            .Add("@FechaVen", SqlDbType.DateTime).Value = CDate(Coleccion(7))
            .Add("@Periodic", SqlDbType.VarChar).Value = Coleccion(8).ToString
            .Add("@Base", SqlDbType.Int).Value = CInt(Coleccion(9))
            .Add("@PlaInv", SqlDbType.VarChar).Value = Coleccion(10).ToString
            .Add("@RendOfre", SqlDbType.Float).Value = CDec(Coleccion(11).TrimEnd("%"))
            .Add("@Dividendo", SqlDbType.Float).Value = CDec(Coleccion(12).TrimEnd("%"))
            .Add("@CodInv ", SqlDbType.VarChar).Value = Codigo.ToString
        End With


        cmd.Connection = cn

        retorno = cmd.ExecuteNonQuery

        cn.Close()
        Return retorno

    End Function


    Public Function ProcesoDatos(ByVal Coleccion As Collection, ByVal Codigo As String, ByVal Diccionario As Dictionary(Of String, Collection), ByVal Estado As String)
        Dim objCon As New Conexion
        Dim retorno As Integer
        Dim sql As String
        Dim cmd As SqlCommand
        Dim myTrans As SqlTransaction
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If

        myTrans = cn.BeginTransaction()
        Try


            If Estado = "Nuevo" Then


                sql = "INSERT INTO [dbo].[FINV]
           ([CodInv]
           ,[ValNominal]	
           ,[CuoPart]	
           ,[ValPart]	
           ,[PorcComisionCasa]	
           ,[PorcComisionBolsa]	
           ,[ComisionCasa]
           ,[ComisionBolsa]
           ,[ValTrans]	
           ,[FechaOpe]	
           ,[FechaLiq]	
           ,[FechaVen]	
           ,[DiasLiq]	
           ,[RendOfre]	
           ,[Periodicidad]	
           ,[Base]	
           ,[Plazo]
           ,[Dividendo]
           ,[TipCalBase]

)
     VALUES
           (@CodInv
           ,@ValNominal	
           ,@CuoPart
           ,@ValPart
           ,@PorcComisionCasa
           ,@PorcComisionBolsa
           ,@ComisionCasa
           ,@ComisionBolsa
           ,@ValTrans
           ,@FechaOpe
           ,@FechaLiq
           ,@FechaVen
           ,@DiasLiq	
           ,@RendOfre	
           ,@Periodicidad	
           ,@Base
           ,@Plazo
           ,@Dividendo
           ,@TipCalBase
)"





            Else
                sql = "UPDATE [dbo].[FINV] SET
         
           [ValNominal]=@ValNominal
           ,[CuoPart]=@CuoPart
           ,[ValPart]=@ValPart
           ,[PorcComisionCasa]=@PorcComisionCasa
           ,[PorcComisionBolsa]=@PorcComisionBolsa
           ,[ComisionCasa]=@ComisionCasa
           ,[ComisionBolsa]=@ComisionBolsa
           ,[ValTrans]=@ValTrans
           ,[FechaOpe]=@FechaOpe
           ,[FechaLiq]=@FechaLiq
           ,[FechaVen]=@FechaVen
           ,[DiasLiq]=@DiasLiq
           ,[RendOfre]=@RendOfre
           ,[Periodicidad]=	@Periodicidad
           ,[Base]=@Base
           ,[Plazo]=@Plazo
           ,[Dividendo]=@Dividendo
           ,[TipCalBase]=@TipCalBase
            where [CodInv]=@CodInv
            
     "
            End If
            cmd = New SqlCommand
            cmd.CommandText = sql
            cmd.Connection = cn






            cmd.Transaction = myTrans
            With cmd.Parameters

                .Add("@CodInv", SqlDbType.VarChar).Value = Codigo
                .Add("@ValNominal", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(1).ToString)
                .Add("@CuoPart", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(2).ToString)
                .Add("@ValPart", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(3).ToString)
                .Add("@PorcComisionCasa", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(4).ToString)
                .Add("@PorcComisionBolsa", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(5).ToString)
                .Add("@ComisionCasa", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(6).ToString)
                .Add("@ComisionBolsa", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(7).ToString)
                .Add("@ValTrans", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(8).ToString)
                .Add("@FechaOpe", SqlDbType.Date).Value = Operaciones.ConvertirFechaBaseDatos(Coleccion(9).ToString)
                .Add("@FechaLiq", SqlDbType.Date).Value = Operaciones.ConvertirFechaBaseDatos(Coleccion(10).ToString)
                .Add("@FechaVen", SqlDbType.Date).Value = Operaciones.ConvertirFechaBaseDatos(Coleccion(11).ToString)
                .Add("@DiasLiq", SqlDbType.Int).Value = Operaciones.ConvertirEntero(Coleccion(12).ToString)
                .Add("@RendOfre", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(13).ToString)
                .Add("@Periodicidad", SqlDbType.VarChar).Value = Coleccion(14).ToString
                .Add("@Base", SqlDbType.Int).Value = Operaciones.ConvertirEntero(Coleccion(15).ToString)
                .Add("@Plazo", SqlDbType.Int).Value = Operaciones.ConvertirEntero(Coleccion(16).ToString)
                .Add("@Dividendo", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(17).ToString)
                .Add("TipCalBase", SqlDbType.VarChar).Value = Coleccion(18).ToString



            End With


            retorno = cmd.ExecuteNonQuery







            sql = "DELETE FROM [dbo].[FINV1]
            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 Diccionario Is Nothing Then


                For Each DatosDiccionario In Diccionario
                    Dim Coleccion2 As Collection = DatosDiccionario.Value
                    sql = "INSERT INTO [dbo].[FINV1]
           ([CodInv]
           ,[NumDiv]
           ,[Fecha]
           ,[Dias]
           ,[Dividendo]
           ,[Ingreso]
           ,[Rend]

        
             
           )
     VALUES
           (@CodInv
           ,@NumDiv
           ,@Fecha
           ,@Dias
           ,@Dividendo
           ,@Ingreso
           ,@Rend
             
          )"

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

                        .Add("@CodInv", SqlDbType.VarChar).Value = Codigo


                        .Add("@NumDiv", SqlDbType.Int).Value = Operaciones.ConvertirEntero(Coleccion2(1).ToString)
                        .Add("@Fecha", SqlDbType.Date).Value = Operaciones.ConvertirFechaBaseDatos(Coleccion2(2).ToString)
                        .Add("@Dias", SqlDbType.Int).Value = Operaciones.ConvertirEntero(Coleccion2(3).ToString)
                        .Add("@Dividendo", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion2(4).ToString)
                        .Add("@Ingreso", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion2(5).ToString)
                        .Add("@Rend", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion2(6).ToString)






                    End With

                    retorno = cmd.ExecuteNonQuery





                Next


            End If
            ''''''''''''''''

            myTrans.Commit()
            MsgBox("Datos Ingresados")
        Catch ex As Exception
            myTrans.Rollback()
            MsgBox(ex.Message)
        End Try
        cn.Close()

        Return retorno


    End Function


    Public Function Nuevo(ByVal Coleccion As Collection, ByVal Codigo 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
        sql = "INSERT INTO [dbo].[FINV](

           [CuoPart]
           ,[VaPart]
           ,[CoCasa]
           ,[CoBolsa]
           ,[DiasLiq]
           ,[FechaOpe]
           ,[FechaVen]
           ,[Periodic]
           ,[Base]
           ,[PlaInv]
           ,[RendOfre]
           ,[Dividendo]
           ,[CodInv]
) VALUES(
           @CuoPart
           ,@VaPart
           ,@CoCasa
           ,@CoBolsa
           ,@DiasLiq
           ,@FechaOpe
           ,@FechaVen
           ,@Periodic
           ,@Base
           ,@PlaInv
           ,@RendOfre
           ,@Dividendo
           ,@CodInv
)
    "




        cmd = New SqlCommand
        cmd.CommandText = sql

        With cmd.Parameters

            .Add("@CuoPart", SqlDbType.Float).Value = CDec(Coleccion(1).TrimEnd("%"))
            .Add("@VaPart", SqlDbType.Float).Value = CDec(Coleccion(2).TrimEnd("%"))
            .Add("@CoCasa", SqlDbType.Float).Value = CDec(Coleccion(3).TrimEnd("%"))
            .Add("@CoBolsa", SqlDbType.Float).Value = CDec(Coleccion(4).TrimEnd("%"))
            .Add("@DiasLiq", SqlDbType.Int).Value = CInt(Coleccion(5))
            .Add("@FechaOpe", SqlDbType.DateTime).Value = CDate(Coleccion(6))
            .Add("@FechaVen", SqlDbType.DateTime).Value = CDate(Coleccion(7))
            .Add("@Periodic", SqlDbType.VarChar).Value = Coleccion(8).ToString
            .Add("@Base", SqlDbType.Int).Value = CInt(Coleccion(9))
            .Add("@PlaInv", SqlDbType.VarChar).Value = Coleccion(10).ToString
            .Add("@RendOfre", SqlDbType.Float).Value = CDec(Coleccion(11).TrimEnd("%"))
            .Add("@Dividendo", SqlDbType.Float).Value = CDec(Coleccion(12).TrimEnd("%"))
            .Add("@CodInv ", SqlDbType.VarChar).Value = Codigo.ToString
        End With


        cmd.Connection = cn

        retorno = cmd.ExecuteNonQuery

        cn.Close()
        Return retorno

    End Function

    Public Function Cargar(ByVal CodInv 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
        sql = "SELECT * FROM [dbo].[FINV] WHERE CodInv=@CodInv"


        cmd = New SqlCommand


        cmd.CommandText = sql
        cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = CodInv
        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(Operaciones.ConvertirDecimal(Datos("ValNominal").ToString))
                Coleccion.Add(Operaciones.ConvertirDecimal(Datos("CuoPart").ToString))
                Coleccion.Add(Operaciones.ConvertirDecimal(Datos("ValPart").ToString))
                Coleccion.Add(Operaciones.ConvertirDecimal(Datos("PorcComisionCasa").ToString))
                Coleccion.Add(Operaciones.ConvertirDecimal(Datos("PorcComisionBolsa").ToString))
                Coleccion.Add(Operaciones.ConvertirDecimal(Datos("ComisionCasa").ToString))
                Coleccion.Add(Operaciones.ConvertirDecimal(Datos("ComisionBolsa").ToString))
                Coleccion.Add(Operaciones.ConvertirDecimal(Datos("ValTrans").ToString))
                Coleccion.Add(Operaciones.ConvertirFecha(Datos("FechaOpe").ToString))
                Coleccion.Add(Operaciones.ConvertirFecha(Datos("FechaLiq").ToString))
                Coleccion.Add(Operaciones.ConvertirFecha(Datos("FechaVen").ToString))
                Coleccion.Add(Operaciones.ConvertirEntero(Datos("DiasLiq").ToString))
                Coleccion.Add(Operaciones.ConvertirDecimal(Datos("RendOfre").ToString))
                Coleccion.Add(Datos("Periodicidad").ToString)
                Coleccion.Add(Operaciones.ConvertirEntero(Datos("Base").ToString))
                Coleccion.Add(Operaciones.ConvertirEntero(Datos("Plazo").ToString))
                Coleccion.Add(Operaciones.ConvertirDecimal(Datos("Dividendo").ToString))
                Coleccion.Add(Datos("TipCalBase").ToString)
                Return Coleccion
            End If
        End If
        cn.Close()
        Return Coleccion
    End Function


    Function GuardarDataGrid(ByVal coleccion As Collection, ByVal Codigo 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
        sql = "INSERT INTO [dbo].[FINV1](

           [CodInv]
           ,[correlativo]
           ,[FechaCorte]
           ,[Dias]
           ,[Dividendo]
           ,[Ingreso]
           ,[RendMensual]
         
      
) VALUES(
           @CodInv
           ,@correlativo
           ,@FechaCorte
           ,@Dias
           ,@Dividendo
           ,@Ingreso
           ,@RendMensual
)
    "




        cmd = New SqlCommand
        cmd.CommandText = sql

        With cmd.Parameters
            .Add("@CodInv", SqlDbType.VarChar).Value = Codigo
            .Add("@correlativo", SqlDbType.Int).Value = CInt(coleccion(1))
            .Add("@FechaCorte", SqlDbType.DateTime).Value = coleccion(2)
            .Add("@Dias", SqlDbType.Int).Value = coleccion(3)
            .Add("@Dividendo", SqlDbType.Float).Value = coleccion(4)
            .Add("@Ingreso", SqlDbType.Float).Value = coleccion(5)
            .Add("@RendMensual", SqlDbType.VarChar).Value = coleccion(6)


        End With


        cmd.Connection = cn

        retorno = cmd.ExecuteNonQuery

        cn.Close()
        Return retorno

    End Function




    Function ModificarDataGrid(ByVal coleccion As Collection, ByVal Codigo 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
        sql = "UPDATE [dbo].[FINV1] SET
           [FechaCorte]=@FechaCorte
           ,[Dias]=@Dias
           ,[Dividendo]=@Dividendo
           ,[Ingreso]=@Ingreso
           ,[RendMensual]=@RendMensual
            WHERE  [CodInv]=@CodInv AND [correlativo]=@correlativo

    "



        cmd = New SqlCommand
        cmd.CommandText = sql

        With cmd.Parameters
            .Add("@CodInv", SqlDbType.VarChar).Value = Codigo
            .Add("@correlativo", SqlDbType.Int).Value = CInt(coleccion(1))
            .Add("@FechaCorte", SqlDbType.DateTime).Value = coleccion(2)
            .Add("@Dias", SqlDbType.Int).Value = coleccion(3)
            .Add("@Dividendo", SqlDbType.Float).Value = coleccion(4)
            .Add("@Ingreso", SqlDbType.Float).Value = coleccion(5)
            .Add("@RendMensual", SqlDbType.VarChar).Value = coleccion(6)


        End With


        cmd.Connection = cn

        retorno = cmd.ExecuteNonQuery

        cn.Close()
        Return retorno

    End Function


    Public Function CargarFilas(ByVal codigo As String)
        Dim objCon As New Conexion
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If
        Dim sql = "select * from FINV1 where CodInv='" + codigo + "'"

        Dim cmd As New SqlCommand(sql, cn)
        Dim dr As SqlDataReader
        Dim Tabla As New DataTable


        dr = cmd.ExecuteReader
        Tabla.Load(dr)
        cn.Close()
        Return Tabla
    End Function


    Public Sub Eliminar(ByVal CodInv As String)



        Dim objCon As New Conexion
        Dim retorno As Integer
        Dim sql As String
        Dim cmd As SqlCommand
        Dim myTrans As SqlTransaction
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If

        myTrans = cn.BeginTransaction()
        Try

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


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


            retorno = cmd.ExecuteNonQuery



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


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


            retorno = cmd.ExecuteNonQuery

            myTrans.Commit()
            MsgBox("Datos Eliminados")
        Catch ex As Exception
            myTrans.Rollback()
            MsgBox(ex.Message)
        End Try
        cn.Close()





    End Sub

    Public Sub EliminarIngreso(ByVal Codigo As String, ByVal NumDiv As Integer)
        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 FINV1
      WHERE [CodInv]=@CodInv AND [NumDiv]=@NumDiv"


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

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

    Public Sub EliminarDatGrid(ByVal Codigo As String, ByVal correlativo As Integer)
        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].[FINV1]
      WHERE [CodInv]=@CodInv AND [correlativo]=@correlativo"




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

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