Imports System.Data.SqlClient

Public Class TitularizacionDAO





    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].[TIT0](
           [CodInv]
           ,[FechaEmision]
           ,[Tasa]
           ,[MontoCompra]
           ,[MontoCompraPrimario]



) VALUES(
           @CodInv
           ,@FechaEmision
           ,@Tasa
           ,@MontoCompra
           ,@MontoCompraPrimario


)
    "





            Else
                sql = "UPDATE [dbo].[TIT0] SET

           [FechaEmision]=@FechaEmision
           ,[Tasa]=@Tasa
           ,[MontoCompra]=@MontoCompra
           ,[MontoCompraPrimario]=@MontoCompraPrimario
         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("@FechaEmision", SqlDbType.DateTime).Value = Coleccion(1)
                .Add("@Tasa", SqlDbType.Float).Value = Coleccion(2)
                .Add("@MontoCompra", SqlDbType.Float).Value = Coleccion(3)

                .Add("@MontoCompraPrimario", SqlDbType.Float).Value = Coleccion(4)


            End With


            retorno = cmd.ExecuteNonQuery

















            If Not Diccionario Is Nothing And Not Diccionario.Count = 0 Then



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




                For Each DatosDiccionario In Diccionario
                    Dim Coleccion2 As Collection = DatosDiccionario.Value
                    sql = "INSERT INTO [dbo].[TIT2](
           [CodInv]
           ,[Correlativo]
           ,[Fecha] 
           ,[Dias] 
           ,[Cuota] 
           ,[AmortCap] 
           ,[Interes] 
           ,[Saldos] 
           ,[Porcentaje]
           ,[Vigente]

) VALUES(
           @CodInv
           ,@Correlativo
           ,@Fecha
           ,@Dias
           ,@Cuota 
           ,@AmortCap 
           ,@Interes 
           ,@Saldos 
           ,@Porcentaje 
           ,@Vigente

)
    "

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

                    With cmd.Parameters
                        .Add("@CodInv", SqlDbType.VarChar).Value = Codigo
                        .Add("@Correlativo", SqlDbType.Float).Value = Coleccion2(1)
                        .Add("@Fecha", SqlDbType.DateTime).Value = Coleccion2(2)
                        .Add("@Dias", SqlDbType.Int).Value = Coleccion2(3)
                        .Add("@Cuota", SqlDbType.Float).Value = Coleccion2(4)
                        .Add("@AmortCap", SqlDbType.Float).Value = Coleccion2(5)
                        .Add("@Interes", SqlDbType.Float).Value = Coleccion2(6)
                        .Add("@Saldos", SqlDbType.Float).Value = Coleccion2(7)
                        .Add("@Porcentaje ", SqlDbType.Float).Value = Coleccion2(8)
                        .Add("@Vigente", SqlDbType.VarChar).Value = Coleccion2(9)

                    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 NuevaTIT(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].[TIT0](
           [CodInv]
           ,[EmisTotal]
           ,[FechaEmision]
           ,[Tasa]
           ,[Periodicidad]
           ,[Periodos]
           ,[MontoCompra]
           ,[MontoCompraPrimario]
           ,[CS1]
           ,[CS2]
           ,[CS3]
           ,[CS4]
           ,[CS5]
           

) VALUES(
           @CodInv
           ,@EmisTotal
           ,@FechaEmision
           ,@Tasa
           ,@Periodicidad
           ,@Periodos
           ,@MontoCompra
           ,@MontoCompraPrimario
           ,@CS1
           ,@CS2
           ,@CS3
           ,@CS4
           ,@CS5

)
    "

        cmd = New SqlCommand
        cmd.CommandText = sql

        With cmd.Parameters
            .Add("@CodInv", SqlDbType.VarChar).Value = Codigo
            .Add("@EmisTotal", SqlDbType.Float).Value = Coleccion(1)
            .Add("@FechaEmision", SqlDbType.DateTime).Value = Coleccion(2)
            .Add("@Tasa", SqlDbType.Float).Value = Coleccion(3)
            .Add("@Periodicidad", SqlDbType.Int).Value = Coleccion(4)
            .Add("@Periodos", SqlDbType.Int).Value = Coleccion(5)
            .Add("@MontoCompra", SqlDbType.Float).Value = Coleccion(6)
            .Add("@MontoCompraPrimario", SqlDbType.Float).Value = Coleccion(7)
            .Add("@CS1", SqlDbType.Float).Value = Coleccion(8)
            .Add("@CS2", SqlDbType.Float).Value = Coleccion(9)
            .Add("@CS3", SqlDbType.Float).Value = Coleccion(10)
            .Add("@CS4", SqlDbType.Float).Value = Coleccion(11)
            .Add("@CS5", SqlDbType.Float).Value = Coleccion(12)

        End With


        cmd.Connection = cn

        retorno = cmd.ExecuteNonQuery

        cn.Close()
        Return retorno

    End Function





    Public Function ModificarTIT(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].[TIT0] SET
           [EmisTotal]=@EmisTotal
           ,[FechaEmision]=@FechaEmision
           ,[Tasa]=@Tasa
           ,[Periodicidad]=@Periodicidad
           ,[Periodos]=@Periodos
           ,[MontoCompra]=@MontoCompra
           ,[MontoCompraPrimario]=@MontoCompraPrimario
           ,[CS1]=@CS1
           ,[CS2]=@CS2
           ,[CS3]=@CS3
           ,[CS4]=@CS4
           ,[CS5]=@CS5
         where [CodInv]=@CodInv
           
        
           
           
           
           
           
           


    "

        cmd = New SqlCommand
        cmd.CommandText = sql

        With cmd.Parameters
            .Add("@CodInv", SqlDbType.VarChar).Value = Codigo
            .Add("@EmisTotal", SqlDbType.Float).Value = Coleccion(1)
            .Add("@FechaEmision", SqlDbType.DateTime).Value = Coleccion(2)
            .Add("@Tasa", SqlDbType.Float).Value = Coleccion(3)
            .Add("@Periodicidad", SqlDbType.Int).Value = Coleccion(4)
            .Add("@Periodos", SqlDbType.Int).Value = Coleccion(5)
            .Add("@MontoCompra", SqlDbType.Float).Value = Coleccion(6)
            .Add("@MontoCompraPrimario", SqlDbType.Float).Value = Coleccion(7)
            .Add("@CS1", SqlDbType.Float).Value = Coleccion(8)
            .Add("@CS2", SqlDbType.Float).Value = Coleccion(9)
            .Add("@CS3", SqlDbType.Float).Value = Coleccion(10)
            .Add("@CS4", SqlDbType.Float).Value = Coleccion(11)
            .Add("@CS5", SqlDbType.Float).Value = Coleccion(12)

        End With


        cmd.Connection = cn

        retorno = cmd.ExecuteNonQuery

        cn.Close()
        Return retorno

    End Function

    Public Function 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].[TIT0]
      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].[TIT2]
      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)
            cn.Close()
            Return False
        End Try
        cn.Close()
        Return True
    End Function



    Public Function NuevaAmortCap(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].[TIT1](
           [CodInv]
           ,[Correlativo]
           ,[Fecha]
           ,[CSFlujo]
           ,[AmortCap]
           ,[Interes]
           ,[Amort]
           ,[Saldos]
           

) VALUES(
           @CodInv
           ,@Correlativo
           ,@Fecha
           ,@CSFlujo
           ,@AmortCap
           ,@Interes
           ,@Amort
           ,@Saldos


)
    "

        cmd = New SqlCommand
        cmd.CommandText = sql

        With cmd.Parameters
            .Add("@CodInv", SqlDbType.VarChar).Value = Codigo
            .Add("@Correlativo", SqlDbType.Int).Value = Coleccion(1)
            .Add("@Fecha", SqlDbType.DateTime).Value = Coleccion(2)
            .Add("@CSFlujo", SqlDbType.Float).Value = Coleccion(3)
            .Add("@AmortCap", SqlDbType.Float).Value = Coleccion(4)
            .Add("@Interes", SqlDbType.Float).Value = Coleccion(5)
            .Add("@Amort", SqlDbType.Float).Value = Coleccion(6)
            .Add("@Saldos", SqlDbType.Float).Value = Coleccion(7)

        End With


        cmd.Connection = cn

        retorno = cmd.ExecuteNonQuery

        cn.Close()
        Return retorno

    End Function




    Public Function ModificarAmortCap(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].[TIT1] SET
            [Fecha]=@Fecha
           ,[CSFlujo]=@CSFlujo
           ,[AmortCap]=@AmortCap
           ,[Interes]=@Interes
           ,[Amort]=@Amort
           ,[Saldos]=@Saldos
           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 = Coleccion(1)
            .Add("@Fecha", SqlDbType.DateTime).Value = Coleccion(2)
            .Add("@CSFlujo", SqlDbType.Float).Value = Coleccion(3)
            .Add("@AmortCap", SqlDbType.Float).Value = Coleccion(4)
            .Add("@Interes", SqlDbType.Float).Value = Coleccion(5)
            .Add("@Amort", SqlDbType.Float).Value = Coleccion(6)
            .Add("@Saldos", SqlDbType.Float).Value = Coleccion(7)

        End With


        cmd.Connection = cn

        retorno = cmd.ExecuteNonQuery

        cn.Close()
        Return retorno

    End Function

    Public Sub EliminarAmortCap(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].[TIT1]
      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









    Public Function NuevaAmort(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].[TIT2](
           [CodInv]
           ,[Correlativo]
           ,[Fecha] 
           ,[Dias] 
           ,[Cuota] 
           ,[AmortCap] 
           ,[Interes] 
           ,[Saldos] 
           ,[Porcentaje]

) VALUES(
           @CodInv
           ,@Correlativo
           ,@Fecha
           ,@Dias
           ,@Cuota 
           ,@AmortCap 
           ,@Interes 
           ,@Saldos 
           ,@Porcentaje 


)
    "

        cmd = New SqlCommand
        cmd.CommandText = sql
        Try

            With cmd.Parameters
                .Add("@CodInv", SqlDbType.VarChar).Value = Codigo
                .Add("@Correlativo", SqlDbType.Int).Value = Coleccion(1)
                .Add("@Fecha", SqlDbType.DateTime).Value = Coleccion(2)
                .Add("@Dias", SqlDbType.Int).Value = Coleccion(3)
                .Add("@Cuota", SqlDbType.Float).Value = Coleccion(4)
                .Add("@AmortCap", SqlDbType.Float).Value = Coleccion(5)
                .Add("@Interes", SqlDbType.Float).Value = Coleccion(6)
                .Add("@Saldos", SqlDbType.Float).Value = Coleccion(7)
                .Add("@Porcentaje ", SqlDbType.Float).Value = Coleccion(8)

            End With


            cmd.Connection = cn

            retorno = cmd.ExecuteNonQuery
            cn.Close()
        Catch ex As Exception
            MsgBox("Error al ingresar datos")
            cn.Close()
            Return Nothing
        End Try

        Return retorno

    End Function














    Public Function ModificarAmort(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].[TIT2] SET
           [Fecha] =@Fecha
           ,[Dias] =@Dias
           ,[Cuota] =@Cuota
           ,[AmortCap]= @AmortCap 
           ,[Interes] =@Interes 
           ,[Saldos] =@Saldos 
           ,[Porcentaje] =@Porcentaje 
           where  [CodInv]=@CodInv AND [Correlativo]=@Correlativo

           
           
           
           
            
           
           
           



    "

        cmd = New SqlCommand
        cmd.CommandText = sql
        Try


            With cmd.Parameters
                .Add("@CodInv", SqlDbType.VarChar).Value = Codigo
                .Add("@Correlativo", SqlDbType.Int).Value = Coleccion(1)
                .Add("@Fecha", SqlDbType.DateTime).Value = Coleccion(2)
                .Add("@Dias", SqlDbType.Int).Value = Coleccion(3)
                .Add("@Cuota", SqlDbType.Float).Value = Coleccion(4)
                .Add("@AmortCap", SqlDbType.Float).Value = Coleccion(5)
                .Add("@Interes", SqlDbType.Float).Value = Coleccion(6)
                .Add("@Saldos", SqlDbType.Float).Value = Coleccion(7)
                .Add("@Porcentaje ", SqlDbType.Float).Value = Coleccion(8)

            End With


            cmd.Connection = cn

            retorno = cmd.ExecuteNonQuery

        Catch ex As Exception
            MsgBox("Error de Modificación")
            cn.Close()
            Return Nothing
        End Try
        cn.Close()
        Return retorno

    End Function



    Public Sub EliminarAmort(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].[TIT2]
      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





    Public Function CargarTIT(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].[TIT0] 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


            If Datos.Read Then

                Coleccion.Add(Datos.Item("FechaEmision"))
                Coleccion.Add(Datos.Item("Tasa"))
                Coleccion.Add(Datos.Item("MontoCompra"))
                Coleccion.Add(Datos.Item("MontoCompraPrimario"))


                Return Coleccion
            End If
        End If
        cn.Close()
        Return Coleccion
    End Function


    Public Function CargarAmortCap(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 TIT1 where CodInv='" + codigo + "'"

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

        Return dr
    End Function

    Public Function CargarAmort(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 TIT2 where CodInv='" + codigo + "' order by Correlativo"

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

        Return dr
    End Function
End Class