Imports System.Data.SqlClient
Public Class DepositosAPlazoDAO

    Dim Operaciones As New Operaciones


    Public Function CargarRegistro(ByVal vCodigoInversion As String) As DepositosAPlazoCE
        Dim objCon As New Conexion
        Dim oDAPCE As New DepositosAPlazoCE
        Dim drd As SqlDataReader
        Dim cmd As SqlCommand
        Dim da As SqlDataAdapter
        Dim sql As String
        Dim dtIntereses As New DataTable
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If
        'INTERESES
        sql = "SELECT * FROM [dbo].[DAP1] T0 WHERE T0.CodInv=@CodInv"
        da = New SqlDataAdapter(sql, cn)
        da.SelectCommand.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = vCodigoInversion
        da.Fill(dtIntereses)
        oDAPCE.dtIntereses = dtIntereses

        sql = "SELECT * FROM [dbo].[DAP0] 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
                oDAPCE.CodigoInversion = vCodigoInversion.ToString
                oDAPCE.MontoDeInversion = Operaciones.ConvertirDecimal(drd.Item("MontoInv").ToString)
                oDAPCE.FechaOperacion = Operaciones.ConvertirFecha(drd.Item("FOper").ToString)
                oDAPCE.FechaVencimiento = Operaciones.ConvertirFecha(drd.Item("FVenc").ToString)
                oDAPCE.Tasa = Operaciones.ConvertirDecimal(drd.Item("Tasa").ToString)
                oDAPCE.Plazo = Operaciones.ConvertirDecimal(drd.Item("Plazo").ToString)
                oDAPCE.Periodicidad = drd.Item("Periodic").ToString

            End If
            drd.Close()

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

        Return oDAPCE

    End Function






    Public Function NuevoDato(ByVal Coleccion As Collection, ByVal Diccionario As Dictionary(Of String, Collection), ByVal Codigo 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 = "INSERT INTO [dbo].[DAP0](
           [CodInv]
           ,[MontoInv]
           ,[FOper]
           ,[FVenc]
           ,[Tasa]
           ,[Plazo]
           ,[Periodic]
           ,[RendBr]
           ,[RendNet]   
           ,[IngrBrutoTot]
           ,[IngrNetoTot]
           ,[Renta]


) VALUES(

           @CodInv
           ,@MontoInv
           ,@FOper
           ,@FVenc
           ,@Tasa
           ,@Plazo
           ,@Periodic
           ,@RendBr
           ,@RendNet
           ,@IngrBrutoTot
           ,@IngrNetoTot
           ,@Renta
)
    "
            cmd = New SqlCommand
            cmd.CommandText = sql
            cmd.Connection = cn






            cmd.Transaction = myTrans
            With cmd.Parameters
                .Add("@CodInv ", SqlDbType.VarChar).Value = Codigo.ToString
                .Add("@MontoInv", SqlDbType.Decimal).Value = Operaciones.ConvertirDecimal(Coleccion(1).ToString)
                .Add("@FOper", SqlDbType.DateTime).Value = Operaciones.ConvertirFecha(Coleccion(2).ToString)
                .Add("@FVenc", SqlDbType.DateTime).Value = Operaciones.ConvertirFecha(Coleccion(3).ToString)
                .Add("@Tasa", SqlDbType.Decimal).Value = Operaciones.ConvertirDecimal(Coleccion(4).ToString)
                .Add("@Plazo", SqlDbType.Int).Value = Operaciones.ConvertirEntero(Coleccion(5).ToString)
                .Add("@Periodic", SqlDbType.VarChar).Value = Coleccion(6).ToString
                .Add("@RendBr", SqlDbType.VarChar).Value = Coleccion(7).ToString
                .Add("@RendNet", SqlDbType.VarChar).Value = Coleccion(8).ToString
                .Add("@IngrBrutoTot", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(9).ToString)
                .Add("@IngrNetoTot", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(10).ToString)
                .Add("@Renta", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(11).ToString)
            End With

            retorno = cmd.ExecuteNonQuery


            sql = "INSERT INTO [dbo].[DAP1](
           [CodInv]
           ,[Correlativo]
           ,[Plazo]
           ,[Fecha]
           ,[IngrBruto]
           ,[PorcImp]
           ,[MontoImp]
           ,[IngrNeto]


) VALUES(

           @CodInv
           ,@Correlativo
           ,@Plazo
           ,@Fecha
           ,@IngrBruto
           ,@PorcImp
           ,@MontoImp
           ,@IngrNeto

)
    "




            If Not Diccionario Is Nothing Then


                For Each DatosDiccionario In Diccionario
                    Dim Coleccion2 As Collection = DatosDiccionario.Value


                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans
                    With cmd.Parameters
                        .Add("@CodInv ", SqlDbType.VarChar).Value = Codigo.ToString
                        .Add("@Correlativo", SqlDbType.Int).Value = Operaciones.ConvertirEntero(Coleccion2(1).ToString)
                        .Add("@Plazo", SqlDbType.Int).Value = Operaciones.ConvertirEntero(Coleccion2(2).ToString)
                        .Add("@Fecha", SqlDbType.DateTime).Value = Operaciones.ConvertirFecha(Coleccion2(3).ToString)
                        .Add("@IngrBruto", SqlDbType.Decimal).Value = Operaciones.ConvertirDecimal(Coleccion2(4).ToString)
                        .Add("@PorcImp", SqlDbType.Decimal).Value = Operaciones.ConvertirDecimal(Coleccion2(5).ToString)
                        .Add("@MontoImp", SqlDbType.Decimal).Value = Operaciones.ConvertirDecimal(Coleccion2(6).ToString)
                        .Add("@IngrNeto", SqlDbType.Decimal).Value = Operaciones.ConvertirDecimal(Coleccion2(7).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 NuevoElemento(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].[DAP1](
           [CodInv]
           ,[Correlativo]
           ,[Plazo]
           ,[Fecha]
           ,[IngrBruto]
           ,[PorcImp]
           ,[MontoImp]
           ,[IngrNeto]


) VALUES(

           @CodInv
           ,@Correlativo
           ,@Plazo
           ,@Fecha
           ,@IngrBruto
           ,@PorcImp
           ,@MontoImp
           ,@IngrNeto

)
    "





        cmd = New SqlCommand
        cmd.CommandText = sql

        With cmd.Parameters



            .Add("@CodInv ", SqlDbType.VarChar).Value = Codigo.ToString
            .Add("@Correlativo", SqlDbType.Int).Value = Operaciones.ConvertirEntero(Coleccion(1).ToString)
            .Add("@Plazo", SqlDbType.Int).Value = Operaciones.ConvertirEntero(Coleccion(2).ToString)
            .Add("@Fecha", SqlDbType.DateTime).Value = Operaciones.ConvertirFecha(Coleccion(3).ToString)
            .Add("@IngrBruto", SqlDbType.Decimal).Value = Operaciones.ConvertirDecimal(Coleccion(4).ToString)
            .Add("@PorcImp", SqlDbType.Decimal).Value = Operaciones.ConvertirDecimal(Coleccion(5).ToString)
            .Add("@MontoImp", SqlDbType.Decimal).Value = Operaciones.ConvertirDecimal(Coleccion(6).ToString)
            .Add("@IngrNeto", SqlDbType.Decimal).Value = Operaciones.ConvertirDecimal(Coleccion(7).ToString)
        End With


        cmd.Connection = cn

        retorno = cmd.ExecuteNonQuery

        cn.Close()
        Return retorno

    End Function





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



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

        cmd.Connection = cn
        cn.Close()
        res = cmd.ExecuteNonQuery

    End Sub










    Public Function ModificarDato(ByVal Coleccion As Collection, ByVal Codigo As String, ByVal Diccionario As Dictionary(Of String, Collection))
        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 = "UPDATE [dbo].[DAP0] SET
            [MontoInv]=@MontoInv
           ,[FOper]=@FOper
           ,[FVenc]=@FVenc
           ,[Tasa]=@Tasa
           ,[Plazo]=@Plazo
           ,[Periodic]=@Periodic
           ,[RendBr]=@RendBr
           ,[RendNet]=@RendNet
            ,[IngrBrutoTot]=@IngrBrutoTot
            ,[IngrNetoTot]=@IngrNetoTot
            ,[Renta]=@Renta
            where [CodInv]=  @CodInv
    "
            cmd = New SqlCommand
            cmd.CommandText = sql
            cmd.Connection = cn






            cmd.Transaction = myTrans
            With cmd.Parameters
                .Add("@CodInv ", SqlDbType.VarChar).Value = Codigo.ToString
                .Add("@MontoInv", SqlDbType.Decimal).Value = Operaciones.ConvertirDecimal(Coleccion(1).ToString)
                .Add("@FOper", SqlDbType.DateTime).Value = Operaciones.ConvertirFecha(Coleccion(2).ToString)
                .Add("@FVenc", SqlDbType.DateTime).Value = Operaciones.ConvertirFecha(Coleccion(3).ToString)
                .Add("@Tasa", SqlDbType.Decimal).Value = Operaciones.ConvertirDecimal(Coleccion(4).ToString)
                .Add("@Plazo", SqlDbType.Int).Value = Operaciones.ConvertirEntero(Coleccion(5).ToString)
                .Add("@Periodic", SqlDbType.VarChar).Value = Coleccion(6).ToString
                .Add("@RendBr", SqlDbType.VarChar).Value = Coleccion(7).ToString
                .Add("@RendNet", SqlDbType.VarChar).Value = Coleccion(8).ToString
                .Add("@IngrBrutoTot", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(9).ToString)
                .Add("@IngrNetoTot", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(10).ToString)
                .Add("@Renta", SqlDbType.Float).Value = Operaciones.ConvertirDecimal(Coleccion(11).ToString)
            End With
            retorno = cmd.ExecuteNonQuery


            sql = "INSERT INTO [dbo].[DAP1](
           [CodInv]
           ,[Correlativo]
           ,[Plazo]
           ,[Fecha]
           ,[IngrBruto]
           ,[PorcImp]
           ,[MontoImp]
           ,[IngrNeto]


) VALUES(

           @CodInv
           ,@Correlativo
           ,@Plazo
           ,@Fecha
           ,@IngrBruto
           ,@PorcImp
           ,@MontoImp
           ,@IngrNeto

)
    "

            sql = "delete from DAP1 Where CodInv='" + Codigo + "'"
            cmd = New SqlCommand
            cmd.CommandText = sql
            cmd.Connection = cn
            cmd.Transaction = myTrans
            retorno = cmd.ExecuteNonQuery
            If Not Diccionario Is Nothing Then


                For Each DatosDiccionario In Diccionario
                    Dim Coleccion2 As Collection = DatosDiccionario.Value
                    sql = "INSERT INTO [dbo].[DAP1](
           [CodInv]
           ,[Correlativo]
           ,[Plazo]
           ,[Fecha]
           ,[IngrBruto]
           ,[PorcImp]
           ,[MontoImp]
           ,[IngrNeto]


) VALUES(

           @CodInv
           ,@Correlativo
           ,@Plazo
           ,@Fecha
           ,@IngrBruto
           ,@PorcImp
           ,@MontoImp
           ,@IngrNeto

)
    "

                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans
                    With cmd.Parameters
                        .Add("@CodInv ", SqlDbType.VarChar).Value = Codigo.ToString
                        .Add("@Correlativo", SqlDbType.Int).Value = Operaciones.ConvertirEntero(Coleccion2(1).ToString)
                        .Add("@Plazo", SqlDbType.Int).Value = Operaciones.ConvertirEntero(Coleccion2(2).ToString)
                        .Add("@Fecha", SqlDbType.DateTime).Value = Operaciones.ConvertirFecha(Coleccion2(3).ToString)
                        .Add("@IngrBruto", SqlDbType.Decimal).Value = Operaciones.ConvertirDecimal(Coleccion2(4).ToString)
                        .Add("@PorcImp", SqlDbType.Decimal).Value = Operaciones.ConvertirDecimal(Coleccion2(5).ToString)
                        .Add("@MontoImp", SqlDbType.Decimal).Value = Operaciones.ConvertirDecimal(Coleccion2(6).ToString)
                        .Add("@IngrNeto", SqlDbType.Decimal).Value = Operaciones.ConvertirDecimal(Coleccion2(7).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 Sub Eliminar(ByVal Codigo 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].[DAP0]
      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



            sql = "DELETE FROM [dbo].[DAP1]
      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

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

    End Sub





End Class