Inversiones/DAO/FuturosDAO.vb


Imports System.Data.SqlClient

Public Class FuturosDAO






    Public Function Modificar(ByVal Codigo As String, ByVal Coleccion As Collection)
        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].[FUT0] SET
[FOperC]=@FOperC
,[FLiqC]=@FLiqC
,[FVenC]=@FVenC
,[VaUnidadC]=@VaUnidadC
,[ValorTransadoC]=@ValorTransadoC
,[CoBrokerC]=@CoBrokerC
,[VaRecC]=@VaRecC
,[IdContratoC]=@IdContratoC
,[CantContratoC]=@CantContratoC
,[UniContraC]=@UniContraC

,[FOperV]=@FOperV
,[FLiqV]=@FLiqV
,[FVenV]=@FVenV
,[VaUnidadV]=@VaUnidadV
,[ValorTransadoV]=@ValorTransadoV
,[CoBrokerV]=@CoBrokerV
,[VaRecV]=@VaRecV
,[IdContratoV]=@IdContratoV
,[CantContratoV]=@CantContratoV
,[UniContraV]=@UniContraV
,[GP]=@GP
,[Rendimiento]=@Rendimiento
,[TipoOperacion]=@TipoOperacion
where  [CodInv]=@CodInv
     
    "

        cmd = New SqlCommand
        cmd.CommandText = sql

        Try



            With cmd.Parameters
                .Add("@CodInv", SqlDbType.VarChar).Value = Codigo.ToString
                .Add("@FOperC", SqlDbType.DateTime).Value = CDate(Coleccion(1))
                .Add("@FLiqC", SqlDbType.DateTime).Value = CDate(Coleccion(2))
                .Add("@FVenC", SqlDbType.DateTime).Value = CDate(Coleccion(3))
                .Add("@VaUnidadC", SqlDbType.Float).Value = CDec(Coleccion(4))
                .Add("@ValorTransadoC", SqlDbType.Float).Value = CDec(Coleccion(5))
                .Add("@CoBrokerC", SqlDbType.Float).Value = CDec(Coleccion(6))
                .Add("@VaRecC", SqlDbType.Float).Value = CDec(Coleccion(7))
                .Add("@IdContratoC", SqlDbType.VarChar).Value = Coleccion(8).ToString
                .Add("@CantContratoC", SqlDbType.Int).Value = CInt(Coleccion(9))
                .Add("@UniContraC", SqlDbType.Int).Value = CInt(Coleccion(10))


                .Add("@FOperV", SqlDbType.DateTime).Value = CDate(Coleccion(11))
                .Add("@FLiqV", SqlDbType.DateTime).Value = CDate(Coleccion(12))
                .Add("@FVenV", SqlDbType.DateTime).Value = CDate(Coleccion(13))
                .Add("@VaUnidadV", SqlDbType.Float).Value = CDec(Coleccion(14))
                .Add("@ValorTransadoV", SqlDbType.Float).Value = CDec(Coleccion(15))
                .Add("@CoBrokerV", SqlDbType.Float).Value = CDec(Coleccion(16))
                .Add("@VaRecV", SqlDbType.Float).Value = CDec(Coleccion(17))
                .Add("@IdContratoV", SqlDbType.VarChar).Value = Coleccion(18).ToString
                .Add("@CantContratoV", SqlDbType.Int).Value = CInt(Coleccion(19))
                .Add("@UniContraV", SqlDbType.Int).Value = CInt(Coleccion(20))

                .Add("@GP", SqlDbType.Float).Value = CDec(Coleccion(21))
                .Add("@Rendimiento", SqlDbType.Float).Value = CDec(Coleccion(22))
                .Add("@TipoOperacion", SqlDbType.VarChar).Value = Coleccion(23).ToString



            End With


            cmd.Connection = cn

            retorno = cmd.ExecuteNonQuery

            MsgBox("Registro modificado")
        Catch ex As Exception
            MsgBox("Error al modificar el registro")
        End Try
        cn.Close()
        Return retorno

    End Function


    Public Function ModificarOpciones(ByVal Codigo As String, ByVal Coleccion As Collection)
        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].[OPC0] SET
[FOperC]=@FOperC
,[FLiqC]=@FLiqC
,[FVenC]=@FVenC
,[VaUnidadC]=@VaUnidadC
,[ValorTransadoC]=@ValorTransadoC
,[CoBrokerC]=@CoBrokerC
,[VaRecC]=@VaRecC
,[IdContratoC]=@IdContratoC
,[CantContratoC]=@CantContratoC
,[UniContraC]=@UniContraC

,[FOperV]=@FOperV
,[FLiqV]=@FLiqV
,[FVenV]=@FVenV
,[VaUnidadV]=@VaUnidadV
,[ValorTransadoV]=@ValorTransadoV
,[CoBrokerV]=@CoBrokerV
,[VaRecV]=@VaRecV
,[IdContratoV]=@IdContratoV
,[CantContratoV]=@CantContratoV
,[UniContraV]=@UniContraV
,[GP]=@GP
,[Rendimiento]=@Rendimiento
,[TipoOperacion]=@TipoOperacion
where  [CodInv]=@CodInv
     
    "

        cmd = New SqlCommand
        cmd.CommandText = sql

        Try



            With cmd.Parameters
                .Add("@CodInv", SqlDbType.VarChar).Value = Codigo.ToString
                .Add("@FOperC", SqlDbType.DateTime).Value = CDate(Coleccion(1))
                .Add("@FLiqC", SqlDbType.DateTime).Value = CDate(Coleccion(2))
                .Add("@FVenC", SqlDbType.DateTime).Value = CDate(Coleccion(3))
                .Add("@VaUnidadC", SqlDbType.Float).Value = CDec(Coleccion(4))
                .Add("@ValorTransadoC", SqlDbType.Float).Value = CDec(Coleccion(5))
                .Add("@CoBrokerC", SqlDbType.Float).Value = CDec(Coleccion(6))
                .Add("@VaRecC", SqlDbType.Float).Value = CDec(Coleccion(7))
                .Add("@IdContratoC", SqlDbType.VarChar).Value = Coleccion(8).ToString
                .Add("@CantContratoC", SqlDbType.Int).Value = CInt(Coleccion(9))
                .Add("@UniContraC", SqlDbType.Int).Value = CInt(Coleccion(10))


                .Add("@FOperV", SqlDbType.DateTime).Value = CDate(Coleccion(11))
                .Add("@FLiqV", SqlDbType.DateTime).Value = CDate(Coleccion(12))
                .Add("@FVenV", SqlDbType.DateTime).Value = CDate(Coleccion(13))
                .Add("@VaUnidadV", SqlDbType.Float).Value = CDec(Coleccion(14))
                .Add("@ValorTransadoV", SqlDbType.Float).Value = CDec(Coleccion(15))
                .Add("@CoBrokerV", SqlDbType.Float).Value = CDec(Coleccion(16))
                .Add("@VaRecV", SqlDbType.Float).Value = CDec(Coleccion(17))
                .Add("@IdContratoV", SqlDbType.VarChar).Value = Coleccion(18).ToString
                .Add("@CantContratoV", SqlDbType.Int).Value = CInt(Coleccion(19))
                .Add("@UniContraV", SqlDbType.Int).Value = CInt(Coleccion(20))

                .Add("@GP", SqlDbType.Float).Value = CDec(Coleccion(21))
                .Add("@Rendimiento", SqlDbType.Float).Value = CDec(Coleccion(22))
                .Add("@TipoOperacion", SqlDbType.VarChar).Value = Coleccion(23).ToString



            End With


            cmd.Connection = cn

            retorno = cmd.ExecuteNonQuery
            MsgBox("Registro modificado")
        Catch ex As Exception
            MsgBox("Error al modificar el registro")
        End Try
        cn.Close()
        Return retorno

    End Function










    Public Function Nuevo(ByVal Codigo As String, ByVal Coleccion As Collection)
        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].[FUT0](
           [CodInv]
,[FOperC]
,[FLiqC]
,[FVenC]
,[VaUnidadC]
,[ValorTransadoC]
,[CoBrokerC]
,[VaRecC]
,[IdContratoC]
,[CantContratoC]
,[UniContraC]

,[FOperV]
,[FLiqV]
,[FVenV]
,[VaUnidadV]
,[ValorTransadoV]
,[CoBrokerV]
,[VaRecV]
,[IdContratoV]
,[CantContratoV]
,[UniContraV]
,[GP]
,[Rendimiento]
,[TipoOperacion]

) VALUES(
           @CodInv
,@FOperC
,@FLiqC
,@FVenC
,@VaUnidadC
,@ValorTransadoC
,@CoBrokerC
,@VaRecC
,@IdContratoC
,@CantContratoC
,@UniContraC

,@FOperV
,@FLiqV
,@FVenV
,@VaUnidadV
,@ValorTransadoV
,@CoBrokerV
,@VaRecV
,@IdContratoV
,@CantContratoV
,@UniContraV
,@GP
,@Rendimiento
,@TipoOperacion


)
    "

        cmd = New SqlCommand
            cmd.CommandText = sql

        'Try



        With cmd.Parameters
                .Add("@CodInv", SqlDbType.VarChar).Value = Codigo.ToString
                .Add("@FOperC", SqlDbType.DateTime).Value = CDate(Coleccion(1))
                .Add("@FLiqC", SqlDbType.DateTime).Value = CDate(Coleccion(2))
                .Add("@FVenC", SqlDbType.DateTime).Value = CDate(Coleccion(3))
                .Add("@VaUnidadC", SqlDbType.Float).Value = CDec(Coleccion(4))
                .Add("@ValorTransadoC", SqlDbType.Float).Value = CDec(Coleccion(5))
                .Add("@CoBrokerC", SqlDbType.Float).Value = CDec(Coleccion(6))
                .Add("@VaRecC", SqlDbType.Float).Value = CDec(Coleccion(7))
                .Add("@IdContratoC", SqlDbType.VarChar).Value = Coleccion(8).ToString
                .Add("@CantContratoC", SqlDbType.Int).Value = CInt(Coleccion(9))
                .Add("@UniContraC", SqlDbType.Int).Value = CInt(Coleccion(10))


                .Add("@FOperV", SqlDbType.DateTime).Value = CDate(Coleccion(11))
                .Add("@FLiqV", SqlDbType.DateTime).Value = CDate(Coleccion(12))
                .Add("@FVenV", SqlDbType.DateTime).Value = CDate(Coleccion(13))
                .Add("@VaUnidadV", SqlDbType.Float).Value = CDec(Coleccion(14))
                .Add("@ValorTransadoV", SqlDbType.Float).Value = CDec(Coleccion(15))
                .Add("@CoBrokerV", SqlDbType.Float).Value = CDec(Coleccion(16))
                .Add("@VaRecV", SqlDbType.Float).Value = CDec(Coleccion(17))
                .Add("@IdContratoV", SqlDbType.VarChar).Value = Coleccion(18).ToString
                .Add("@CantContratoV", SqlDbType.Int).Value = CInt(Coleccion(19))
                .Add("@UniContraV", SqlDbType.Int).Value = CInt(Coleccion(20))

                .Add("@GP", SqlDbType.Float).Value = CDec(Coleccion(21))
                .Add("@Rendimiento", SqlDbType.Float).Value = CDec(Coleccion(22))
                .Add("@TipoOperacion", SqlDbType.VarChar).Value = Coleccion(23).ToString



            End With


            cmd.Connection = cn

            retorno = cmd.ExecuteNonQuery
            MsgBox("Registro Creado")
        'Catch ex As Exception
        '    MsgBox("Error al crear el registro")
        'End Try
        cn.Close()
        Return retorno

        End Function



    Public Function NuevoOpciones(ByVal Codigo As String, ByVal Coleccion As Collection)
        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].[OPC0](
           [CodInv]
,[FOperC]
,[FLiqC]
,[FVenC]
,[VaUnidadC]
,[ValorTransadoC]
,[CoBrokerC]
,[VaRecC]
,[IdContratoC]
,[CantContratoC]
,[UniContraC]

,[FOperV]
,[FLiqV]
,[FVenV]
,[VaUnidadV]
,[ValorTransadoV]
,[CoBrokerV]
,[VaRecV]
,[IdContratoV]
,[CantContratoV]
,[UniContraV]
,[GP]
,[Rendimiento]
,[TipoOperacion]

) VALUES(
           @CodInv
,@FOperC
,@FLiqC
,@FVenC
,@VaUnidadC
,@ValorTransadoC
,@CoBrokerC
,@VaRecC
,@IdContratoC
,@CantContratoC
,@UniContraC

,@FOperV
,@FLiqV
,@FVenV
,@VaUnidadV
,@ValorTransadoV
,@CoBrokerV
,@VaRecV
,@IdContratoV
,@CantContratoV
,@UniContraV
,@GP
,@Rendimiento
,@TipoOperacion

)
    "

        cmd = New SqlCommand
        cmd.CommandText = sql

        Try



            With cmd.Parameters
                .Add("@CodInv", SqlDbType.VarChar).Value = Codigo.ToString
                .Add("@FOperC", SqlDbType.DateTime).Value = CDate(Coleccion(1))
                .Add("@FLiqC", SqlDbType.DateTime).Value = CDate(Coleccion(2))
                .Add("@FVenC", SqlDbType.DateTime).Value = CDate(Coleccion(3))
                .Add("@VaUnidadC", SqlDbType.Float).Value = CDec(Coleccion(4))
                .Add("@ValorTransadoC", SqlDbType.Float).Value = CDec(Coleccion(5))
                .Add("@CoBrokerC", SqlDbType.Float).Value = CDec(Coleccion(6))
                .Add("@VaRecC", SqlDbType.Float).Value = CDec(Coleccion(7))
                .Add("@IdContratoC", SqlDbType.VarChar).Value = Coleccion(8).ToString
                .Add("@CantContratoC", SqlDbType.Int).Value = CInt(Coleccion(9))
                .Add("@UniContraC", SqlDbType.Int).Value = CInt(Coleccion(10))


                .Add("@FOperV", SqlDbType.DateTime).Value = CDate(Coleccion(11))
                .Add("@FLiqV", SqlDbType.DateTime).Value = CDate(Coleccion(12))
                .Add("@FVenV", SqlDbType.DateTime).Value = CDate(Coleccion(13))
                .Add("@VaUnidadV", SqlDbType.Float).Value = CDec(Coleccion(14))
                .Add("@ValorTransadoV", SqlDbType.Float).Value = CDec(Coleccion(15))
                .Add("@CoBrokerV", SqlDbType.Float).Value = CDec(Coleccion(16))
                .Add("@VaRecV", SqlDbType.Float).Value = CDec(Coleccion(17))
                .Add("@IdContratoV", SqlDbType.VarChar).Value = Coleccion(18).ToString
                .Add("@CantContratoV", SqlDbType.Int).Value = CInt(Coleccion(19))
                .Add("@UniContraV", SqlDbType.Int).Value = CInt(Coleccion(20))

                .Add("@GP", SqlDbType.Float).Value = CDec(Coleccion(21))
                .Add("@Rendimiento", SqlDbType.Float).Value = CDec(Coleccion(22))
                .Add("@TipoOperacion", SqlDbType.VarChar).Value = Coleccion(23).ToString



            End With


            cmd.Connection = cn

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

    End Function


    Public Function CargarDatos(ByVal Codigo As String)
        Dim objCon As New Conexion
        Dim sql As String
        Dim cmd As SqlCommand
        Dim Coleccion As New Collection
        Dim Err As Boolean = False
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If
        sql = "SELECT * FROM [dbo].[FUT0] WHERE CodInv=@Codigo"


        cmd = New SqlCommand


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

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



                If Datos.Read Then

                    Coleccion.Add(Datos.Item("FOperC").ToString)
                    Coleccion.Add(Datos.Item("FLiqC").ToString)
                    Coleccion.Add(Datos.Item("FVenC").ToString)
                    Coleccion.Add(Datos.Item("VaUnidadC").ToString)
                    Coleccion.Add(Datos.Item("ValorTransadoC").ToString)
                    Coleccion.Add(Datos.Item("CoBrokerC").ToString)
                    Coleccion.Add(Datos.Item("VaRecC").ToString)
                    Coleccion.Add(Datos.Item("IdContratoC").ToString)
                    Coleccion.Add(Datos.Item("CantContratoC").ToString)
                    Coleccion.Add(Datos.Item("UniContraC").ToString)


                    Coleccion.Add(Datos.Item("FOperV").ToString)
                    Coleccion.Add(Datos.Item("FLiqV").ToString)
                    Coleccion.Add(Datos.Item("FVenV").ToString)
                    Coleccion.Add(Datos.Item("VaUnidadV").ToString)
                    Coleccion.Add(Datos.Item("ValorTransadoV").ToString)
                    Coleccion.Add(Datos.Item("CoBrokerV").ToString)
                    Coleccion.Add(Datos.Item("VaRecV").ToString)
                    Coleccion.Add(Datos.Item("IdContratoV").ToString)
                    Coleccion.Add(Datos.Item("CantContratoV").ToString)
                    Coleccion.Add(Datos.Item("UniContraV").ToString)

                    Coleccion.Add(Datos.Item("GP").ToString)
                    Coleccion.Add(Datos.Item("Rendimiento").ToString)
                    Coleccion.Add(Datos.Item("TipoOperacion").ToString)


                    Return Coleccion
                Else

                    Err = True
                End If
            Catch ex As Exception
                MsgBox("Ha Ocurrido un error interno")
            End Try
        Else

        End If
        Coleccion.Add(False)
        cn.Close()
        Return Coleccion

    End Function




    Public Function CargarDatosOpciones(ByVal Codigo 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].[OPC0] WHERE CodInv=@Codigo"


        cmd = New SqlCommand


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

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



                If Datos.Read Then

                    Coleccion.Add(Datos.Item("FOperC"))
                    Coleccion.Add(Datos.Item("FLiqC"))
                    Coleccion.Add(Datos.Item("FVenC"))
                    Coleccion.Add(Datos.Item("VaUnidadC"))
                    Coleccion.Add(Datos.Item("ValorTransadoC"))
                    Coleccion.Add(Datos.Item("CoBrokerC"))
                    Coleccion.Add(Datos.Item("VaRecC"))
                    Coleccion.Add(Datos.Item("IdContratoC"))
                    Coleccion.Add(Datos.Item("CantContratoC"))
                    Coleccion.Add(Datos.Item("UniContraC"))


                    Coleccion.Add(Datos.Item("FOperV"))
                    Coleccion.Add(Datos.Item("FLiqV"))
                    Coleccion.Add(Datos.Item("FVenV"))
                    Coleccion.Add(Datos.Item("VaUnidadV"))
                    Coleccion.Add(Datos.Item("ValorTransadoV"))
                    Coleccion.Add(Datos.Item("CoBrokerV"))
                    Coleccion.Add(Datos.Item("VaRecV"))
                    Coleccion.Add(Datos.Item("IdContratoV"))
                    Coleccion.Add(Datos.Item("CantContratoV"))
                    Coleccion.Add(Datos.Item("UniContraV"))

                    Coleccion.Add(Datos.Item("GP"))
                    Coleccion.Add(Datos.Item("Rendimiento"))
                    Coleccion.Add(Datos.Item("TipoOperacion").ToString)


                    Return Coleccion
                End If
            Catch ex As Exception
                MsgBox("Ha Ocurrido un error interno")
            End Try
        End If
        Coleccion.Add(False)
        cn.Close()
        Return Coleccion
    End Function











































    Public Sub Eliminar(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].[FUT0]
      WHERE [CodInv]=@CodInv "


        Try


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

            cmd.Connection = cn

            res = cmd.ExecuteNonQuery
            MsgBox("Registro Eliminado")
        Catch ex As Exception
            MsgBox("No se pudo Eliminar")
        End Try
        cn.Close()
    End Sub



    Public Sub EliminarOpciones(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].[OPC0]
      WHERE [CodInv]=@CodInv "


        Try


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

            cmd.Connection = cn

            res = cmd.ExecuteNonQuery
            MsgBox("Registro Eliminado")
        Catch ex As Exception
            MsgBox("No se pudo Eliminar")
        End Try
        cn.Close()
    End Sub
End Class