Imports System.Data.SqlClient
Public Class PrestamoEmpresarialDAO




    Public Function ProcesoDatos(ByVal Coleccion As Collection, ByVal Codigo As String, ByVal Diccionario2 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].[PEMP0](
           [CodInv]
            ,[monto]
            ,[fechaoper]
            ,[fechaven]
            ,[tasa]
            ,[periodicidad]
            ,[base]
            ,[tipotasa]
            ,[comisiondesembolso]
            ,[plazodias]

) VALUES(
           @CodInv
            ,@monto
            ,@fechaoper
            ,@fechaven
            ,@tasa
            ,@periodicidad
            ,@base
            ,@tipotasa
            ,@comisiondesembolso
            ,@plazodias
)
    "

            Else
                sql = "UPDATE [dbo].[PEMP0] SET
          
            [monto]=@monto
            ,[fechaoper]=@fechaoper
            ,[fechaven]=@fechaven
            ,[tasa]=@tasa
            ,[periodicidad]=@periodicidad
            ,[base]=@base
            ,[tipotasa]=@tipotasa
            ,[comisiondesembolso]=@comisiondesembolso
            ,[plazodias]=@plazodias
            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("@monto", SqlDbType.Float).Value = CDec(Coleccion(1))
                .Add("@fechaoper", SqlDbType.DateTime).Value = CDate(Coleccion(2))
                .Add("@fechaven", SqlDbType.DateTime).Value = CDate(Coleccion(3))
                .Add("@tasa", SqlDbType.Float).Value = CDec(Coleccion(4))
                .Add("@periodicidad", SqlDbType.VarChar).Value = Coleccion(5)
                .Add("@base", SqlDbType.Int).Value = CInt(Coleccion(6))
                .Add("@tipotasa", SqlDbType.VarChar).Value = Coleccion(7)
                .Add("@comisiondesembolso", SqlDbType.Float).Value = CDec(Coleccion(8))
                .Add("@plazodias", SqlDbType.Int).Value = CInt(Coleccion(9))


            End With


            retorno = cmd.ExecuteNonQuery













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


                For Each DatosDiccionario In Diccionario2
                    Dim Coleccion2 As Collection = DatosDiccionario.Value
                    sql = "INSERT INTO [dbo].[PEMP1](
           [CodInv]
            ,[correlativo]
            ,[plazo]
            ,[fechapago]
            ,[ingresarcomision]
            ,[ingresarinteres]
            ,[IVA]
            ,[amortizacion]
            ,[montorecibir]
            ,[saldopendiente]
            ,[fechacorte]

) VALUES(
           @CodInv
            ,@correlativo
            ,@plazo
            ,@fechapago
            ,@ingresarcomision
            ,@ingresarinteres
            ,@IVA
            ,@amortizacion
            ,@montorecibir
            ,@saldopendiente
            ,@fechacorte

)
    "

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


                        .Add("@CodInv", SqlDbType.VarChar).Value = Codigo
                        .Add("@correlativo", SqlDbType.Int).Value = Coleccion2(1)
                        .Add("@plazo", SqlDbType.Int).Value = Coleccion2(2)
                        .Add("@fechapago", SqlDbType.Date).Value = Coleccion2(3)
                        .Add("@ingresarcomision", SqlDbType.Float).Value = Coleccion2(4)
                        .Add("@ingresarinteres", SqlDbType.Float).Value = Coleccion2(5)
                        .Add("@IVA", SqlDbType.Float).Value = Coleccion2(6)
                        .Add("@amortizacion", SqlDbType.Float).Value = Coleccion2(7)
                        .Add("@saldopendiente", SqlDbType.Float).Value = Coleccion2(8)
                        .Add("@montorecibir", SqlDbType.Float).Value = Coleccion2(9)


                    If Coleccion2(10) Is DBNull.Value Then
                        .Add("@fechacorte", SqlDbType.Date).Value = DBNull.Value
                    Else


                        If String.IsNullOrEmpty(Coleccion2(10)) Then
                            .Add("@fechacorte", SqlDbType.Date).Value = DBNull.Value
                        Else
                            .Add("@fechacorte", SqlDbType.Date).Value = Coleccion2(10)
                        End If


                    End If


                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 NuevoDato(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].[PEMP0](
           [CodInv]
            ,[monto]
            ,[fechaoper]
            ,[fechaven]
            ,[tasa]
            ,[periodicidad]
            ,[base]
            ,[tipotasa]
            ,[comisiondesembolso]
            ,[plazodias]

) VALUES(
           @CodInv
            ,@monto
            ,@fechaoper
            ,@fechaven
            ,@tasa
            ,@periodicidad
            ,@base
            ,@tipotasa
            ,@comisiondesembolso
            ,@plazodias
)
    "


        cmd = New SqlCommand
        cmd.CommandText = sql

        With cmd.Parameters
            .Add("@CodInv", SqlDbType.VarChar).Value = Codigo
            .Add("@monto", SqlDbType.Float).Value = CDec(Coleccion(1))
            .Add("@fechaoper", SqlDbType.DateTime).Value = CDate(Coleccion(2))
            .Add("@fechaven", SqlDbType.DateTime).Value = CDate(Coleccion(3))
            .Add("@tasa", SqlDbType.Float).Value = CDec(Coleccion(4))
            .Add("@periodicidad", SqlDbType.VarChar).Value = Coleccion(5)
            .Add("@base", SqlDbType.Int).Value = CInt(Coleccion(6))
            .Add("@tipotasa", SqlDbType.VarChar).Value = Coleccion(7)
            .Add("@comisiondesembolso", SqlDbType.Float).Value = CDec(Coleccion(8))
            .Add("@plazodias", SqlDbType.Int).Value = CInt(Coleccion(9))


        End With


        cmd.Connection = cn

        retorno = cmd.ExecuteNonQuery

        cn.Close()
        Return retorno

    End Function





    Public Function ModificarDato(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].[PEMP0] SET
          
            [monto]=@monto
            ,[fechaoper]=@fechaoper
            ,[fechaven]=@fechaven
            ,[tasa]=@tasa
            ,[periodicidad]=@periodicidad
            ,[base]=@base
            ,[tipotasa]=@tipotasa
            ,[comisiondesembolso]=@comisiondesembolso
            ,[plazodias]=@plazodias
            where  [CodInv]=@CodInv
    "

        cmd = New SqlCommand
        cmd.CommandText = sql

        With cmd.Parameters
            .Add("@CodInv", SqlDbType.VarChar).Value = Codigo
            .Add("@monto", SqlDbType.Float).Value = CDec(Coleccion(1))
            .Add("@fechaoper", SqlDbType.DateTime).Value = CDate(Coleccion(2))
            .Add("@fechaven", SqlDbType.DateTime).Value = CDate(Coleccion(3))
            .Add("@tasa", SqlDbType.Float).Value = CDec(Coleccion(4))
            .Add("@periodicidad", SqlDbType.VarChar).Value = Coleccion(5)
            .Add("@base", SqlDbType.Int).Value = CInt(Coleccion(6))
            .Add("@tipotasa", SqlDbType.VarChar).Value = Coleccion(7)
            .Add("@comisiondesembolso", SqlDbType.Float).Value = CDec(Coleccion(8))
            .Add("@plazodias", SqlDbType.Int).Value = CInt(Coleccion(9))


        End With


        cmd.Connection = cn

        retorno = cmd.ExecuteNonQuery

        cn.Close()
        Return retorno

    End Function







    Public Function NuevoDetalle(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].[PEMP1](
           [CodInv]
            ,[correlativo]
            ,[plazo]
            ,[fechapago]
            ,[ingresarcomision]
            ,[ingresarinteres]
            ,[IVA]
            ,[amortizacion]
            ,[montorecibir]
            ,[saldopendiente]
            ,[fechacorte]

) VALUES(
           @CodInv
            ,@correlativo
            ,@plazo
            ,@fechapago
            ,@ingresarcomision
            ,@ingresarinteres
            ,@IVA
            ,@amortizacion
            ,@montorecibir
            ,@saldopendiente
            ,@fechacorte

)
    "

        cmd = New SqlCommand
        cmd.CommandText = sql



        With cmd.Parameters


                .Add("@CodInv", SqlDbType.VarChar).Value = Codigo
                .Add("@correlativo", SqlDbType.Int).Value = Coleccion(1)
                .Add("@plazo", SqlDbType.Int).Value = Coleccion(2)
                .Add("@fechapago", SqlDbType.Date).Value = Coleccion(3)
                .Add("@ingresarcomision", SqlDbType.Float).Value = Coleccion(4)
                .Add("@ingresarinteres", SqlDbType.Float).Value = Coleccion(5)
                .Add("@IVA", SqlDbType.Float).Value = Coleccion(6)
                .Add("@amortizacion", SqlDbType.Float).Value = Coleccion(7)
                .Add("@saldopendiente", SqlDbType.Float).Value = Coleccion(8)
                .Add("@montorecibir", SqlDbType.Float).Value = Coleccion(9)



                If String.IsNullOrEmpty(Coleccion(10)) Then
                    .Add("@fechacorte", SqlDbType.Date).Value = DBNull.Value
                Else
                    .Add("@fechacorte", SqlDbType.Date).Value = Coleccion(10)
                End If





            End With


            cmd.Connection = cn

            retorno = cmd.ExecuteNonQuery

        cn.Close()
        Return retorno

    End Function









    Public Function ModificarDetalle(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].[PEMP1] SET
            [plazo]=@plazo
            ,[fechapago]=@fechapago
            ,[ingresarcomision]=@ingresarcomision
            ,[ingresarinteres]=@ingresarinteres
            ,[IVA]=@IVA
            ,[amortizacion]=@amortizacion
            ,[montorecibir]=@montorecibir
            ,[saldopendiente]=@saldopendiente
            ,[fechacorte]=@fechacorte
            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("@plazo", SqlDbType.Int).Value = Coleccion(2)
                .Add("@fechapago", SqlDbType.Date).Value = Coleccion(3)
                .Add("@ingresarcomision", SqlDbType.Float).Value = Coleccion(4)
                .Add("@ingresarinteres", SqlDbType.Float).Value = Coleccion(5)
                .Add("@IVA", SqlDbType.Float).Value = Coleccion(6)
                .Add("@amortizacion", SqlDbType.Float).Value = Coleccion(7)
                .Add("@saldopendiente", SqlDbType.Float).Value = Coleccion(8)
                .Add("@montorecibir", SqlDbType.Float).Value = Coleccion(9)


                If Coleccion(10) Is DBNull.Value Then
                    .Add("@fechacorte", SqlDbType.Date).Value = DBNull.Value


                Else
                    If String.IsNullOrEmpty(Coleccion(10)) Then
                        .Add("@fechacorte", SqlDbType.Date).Value = DBNull.Value
                    Else
                        .Add("@fechacorte", SqlDbType.Date).Value = Coleccion(10)
                    End If


                End If



            End With

            cmd.Connection = cn

            retorno = cmd.ExecuteNonQuery
        Catch ex As Exception
            Return Nothing
        End Try
        cn.Close()
        Return retorno

    End Function





    Public Function CargarDato(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].[PEMP0] 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("monto"))
                Coleccion.Add(Datos.Item("fechaoper"))
                Coleccion.Add(Datos.Item("fechaven"))
                Coleccion.Add(Datos.Item("tasa").ToString + "%")
                Coleccion.Add(Datos.Item("periodicidad"))
                Coleccion.Add(Datos.Item("base"))
                Coleccion.Add(Datos.Item("tipotasa"))
                Coleccion.Add(Datos.Item("comisiondesembolso").ToString + "%")
                Coleccion.Add(Datos.Item("plazodias"))

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





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

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



        dr = cmd.ExecuteReader


        Return dr
    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].[PEMP0]
      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].[PEMP1]
      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

    Public Function EliminarDetalles(ByVal Correlativo As Integer, 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].[PEMP1]
      WHERE [CodInv]=@CodInv and [Correlativo]=@Correlativo"



        Try


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

            cmd.Connection = cn

            res = cmd.ExecuteNonQuery
        Catch ex As Exception
            cn.Close()
            Return Nothing
        End Try
        cn.Close()
        Return 1
    End Function
End Class