Inversiones/DAO/IngresosDAO.vb

Imports System.Data.SqlClient
Module IngresosDAO
    Public Function ListaIngresos(ByVal vFechaI As Date, vFechaF As Date, vSoloPendientes As Boolean) As DataTable
        Dim cn As New Conexion
        Dim dt As New DataTable
        Dim da As SqlDataAdapter
        Dim pSoloPendientes As Integer

        If vSoloPendientes Then
            pSoloPendientes = 1
        Else
            pSoloPendientes = 0
        End If

        da = New SqlDataAdapter("SP_Ingresos_Master", cn.Conectar)
        da.SelectCommand.CommandType = CommandType.StoredProcedure
        da.SelectCommand.Parameters.Add("@FechaI", SqlDbType.DateTime).Value = vFechaI
        da.SelectCommand.Parameters.Add("@FechaF", SqlDbType.DateTime).Value = vFechaF
        da.SelectCommand.Parameters.Add("@SoloPendientes", SqlDbType.Bit).Value = pSoloPendientes
        da.Fill(dt)
        Return dt


    End Function

    Public Function DetalleDeIngreso(ByVal vCodInv As String, ByVal vIngresoID As Integer) As IngresoDetalleCE
        Dim oIngresoDetalleCE As New IngresoDetalleCE
        Dim oConexion As New Conexion
        Dim cn As SqlConnection
        Dim cmd As SqlCommand
        Dim vSqlParameter As SqlParameter
        Dim dr As SqlDataReader

        cmd = New SqlCommand
        cmd.CommandText = "SP_IngresosDetalle"
        cmd.CommandType = CommandType.StoredProcedure

        vSqlParameter = New SqlParameter
        vSqlParameter.ParameterName = "@CodInv"
        vSqlParameter.SqlDbType = SqlDbType.VarChar
        vSqlParameter.Direction = ParameterDirection.Input
        vSqlParameter.Value = vCodInv
        cmd.Parameters.Add(vSqlParameter)

        vSqlParameter = New SqlParameter
        vSqlParameter.ParameterName = "@IngresoId"
        vSqlParameter.SqlDbType = SqlDbType.Int
        vSqlParameter.Direction = ParameterDirection.Input
        vSqlParameter.Value = vIngresoID
        cmd.Parameters.Add(vSqlParameter)

        cn = oConexion.Conectar
        If Not cn.State = ConnectionState.Open Then
            cn.Open()
        End If

        cmd.Connection = cn
        dr = cmd.ExecuteReader()

        If dr.HasRows Then
            'oIngresoDetalleCE = New IngresoDetalleCE

            While dr.Read
                oIngresoDetalleCE.CodigoInversion = dr.Item("CodInv").ToString
                oIngresoDetalleCE.CodigoInstrumentoFinanciero = dr.Item("CodIF").ToString
                oIngresoDetalleCE.NombreManualIF = dr.Item("NombreManIF").ToString
                oIngresoDetalleCE.CodigoManualIF = dr.Item("CodManIF").ToString
                oIngresoDetalleCE.CodigoEmpresa = dr.Item("CodEmpr").ToString
                If Not Date.TryParse(dr.Item("FechaVencimiento"), oIngresoDetalleCE.FechaVencimiento) Then
                    oIngresoDetalleCE.FechaVencimiento = Date.MinValue.Date
                End If
                Integer.TryParse(dr.Item("Correlativo"), oIngresoDetalleCE.IngresoId)
                Double.TryParse(dr.Item("Capital").ToString, oIngresoDetalleCE.MontoCapitalProyectado)
                Double.TryParse(dr.Item("IngresoBruto"), oIngresoDetalleCE.MontoIngresoBrutoProyectado)
                Double.TryParse(dr.Item("ISR"), oIngresoDetalleCE.MontoISRProyectado)
                Double.TryParse(dr.Item("IngresoNeto"), oIngresoDetalleCE.MontoIngresoNetoProyectado)
                oIngresoDetalleCE.EstadoDelIngreso = dr.Item("EstadoIngreso").ToString
                oIngresoDetalleCE.SAP_BD = dr.Item("SAP_BD").ToString

            End While
        End If

        Return oIngresoDetalleCE
    End Function

    Public Function ValidarIngreso(ByVal pCodInv As String, ByVal pIngresoId As Integer) As Integer
        Dim res As Integer
        Dim oConexion As New Conexion
        Dim cn As SqlConnection
        Dim cmd As SqlCommand
        Dim vSqlParameter As SqlParameter
        Dim query As String

        query = "UPDATE [dbo].[INGR]
SET	[FechaValidacion] = @FechaValidacion
	,[Validado] = 'S'
 WHERE
	[CodInv]=@CodInv 
	AND [IngresoID]=@IngresoID
"
        cn = oConexion.Conectar
        cmd = New SqlCommand()

        cmd.CommandText = query
        cmd.CommandType = CommandType.Text

        vSqlParameter = New SqlParameter
        vSqlParameter.ParameterName = "@CodInv"
        vSqlParameter.SqlDbType = SqlDbType.VarChar
        vSqlParameter.Direction = ParameterDirection.Input
        vSqlParameter.Value = pCodInv
        cmd.Parameters.Add(vSqlParameter)

        vSqlParameter = New SqlParameter
        vSqlParameter.ParameterName = "@IngresoId"
        vSqlParameter.SqlDbType = SqlDbType.Int
        vSqlParameter.Direction = ParameterDirection.Input
        vSqlParameter.Value = pIngresoId
        cmd.Parameters.Add(vSqlParameter)

        vSqlParameter = New SqlParameter
        vSqlParameter.ParameterName = "@FechaValidacion"
        vSqlParameter.SqlDbType = SqlDbType.VarChar
        vSqlParameter.Direction = ParameterDirection.Input
        vSqlParameter.Value = Date.Now.Date
        cmd.Parameters.Add(vSqlParameter)

        If Not cn.State = ConnectionState.Open Then
            cn.Open()
        End If

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

        Return res
    End Function
    Public Function ConciliarIngreso(ByVal pCodInv As String, ByVal pIngresoId As Integer) As Integer
        Dim res As Integer
        Dim oConexion As New Conexion
        Dim cn As SqlConnection
        Dim cmd As SqlCommand
        Dim vSqlParameter As SqlParameter
        Dim query As String

        query = "UPDATE [dbo].[INGR]
SET	[FechaConciliacion] = @FechaConciliacion
	,[Conciliado] = 'S'
 WHERE
	[CodInv]=@CodInv 
	AND [IngresoID]=@IngresoID
"
        cn = oConexion.Conectar
        cmd = New SqlCommand()

        cmd.CommandText = query
        cmd.CommandType = CommandType.Text

        vSqlParameter = New SqlParameter
        vSqlParameter.ParameterName = "@CodInv"
        vSqlParameter.SqlDbType = SqlDbType.VarChar
        vSqlParameter.Direction = ParameterDirection.Input
        vSqlParameter.Value = pCodInv
        cmd.Parameters.Add(vSqlParameter)

        vSqlParameter = New SqlParameter
        vSqlParameter.ParameterName = "@IngresoId"
        vSqlParameter.SqlDbType = SqlDbType.Int
        vSqlParameter.Direction = ParameterDirection.Input
        vSqlParameter.Value = pIngresoId
        cmd.Parameters.Add(vSqlParameter)

        vSqlParameter = New SqlParameter
        vSqlParameter.ParameterName = "@FechaConciliacion"
        vSqlParameter.SqlDbType = SqlDbType.VarChar
        vSqlParameter.Direction = ParameterDirection.Input
        vSqlParameter.Value = Date.Now.Date
        cmd.Parameters.Add(vSqlParameter)

        If Not cn.State = ConnectionState.Open Then
            cn.Open()
        End If

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

        Return res
    End Function
    Public Function RegistrarIngreso(ByVal pRegistroIngresoCE As RegistroIngresoCE) As Integer
        Dim query As String
        Dim cmd As SqlCommand
        Dim oConexion As New Conexion
        Dim cn As SqlConnection
        Dim retorno As Integer

        query = "INSERT INTO [dbo].[INGR]
           ([CodEmpr]
           ,[CodIF]
           ,[CodBanco]
           ,[CodInv]
           ,[IngresoID]
           ,[FormaPago]
           ,[CuentaBanco]
           ,[FechaRegistro]
           ,[Registrado]
,[FechaIngreso]
,[MontoCapital]
,[MontoIngresoBruto]
,[MontoISR]
,[MontoIngresoNeto]
,[MontoIngresoLiquido]
,[TransId]
)
     VALUES
           (@CodEmpr
           ,@CodIF
           ,@CodBanco
           ,@CodInv
           ,@IngresoID
           ,@FormaPago
           ,@CuentaBanco
           ,@FechaRegistro
           ,'S'
,@FechaIngreso
,@MontoCapital
,@MontoIngresoBruto
,@MontoISR
,@MontoIngresoNeto
,@MontoIngresoLiquido
,@TransId
)
		   "

        cmd = New SqlCommand
        cmd.CommandText = query
        cmd.Parameters.Add("@CodEmpr", SqlDbType.VarChar).Value = pRegistroIngresoCE.CodigoEmpresa
        cmd.Parameters.Add("@CodIF", SqlDbType.VarChar).Value = pRegistroIngresoCE.CodigoInstrumento
        cmd.Parameters.Add("@CodBanco", SqlDbType.VarChar).Value = pRegistroIngresoCE.CodigoBanco
        cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = pRegistroIngresoCE.CodigoInversion
        cmd.Parameters.Add("@IngresoID", SqlDbType.Int).Value = pRegistroIngresoCE.IngresoID
        cmd.Parameters.Add("@FormaPago", SqlDbType.VarChar).Value = pRegistroIngresoCE.FormaDePago
        cmd.Parameters.Add("@CuentaBanco", SqlDbType.VarChar).Value = pRegistroIngresoCE.CuentaBancaria
        cmd.Parameters.Add("@FechaRegistro", SqlDbType.DateTime).Value = pRegistroIngresoCE.FechaRegistro
        cmd.Parameters.Add("@FechaIngreso", SqlDbType.DateTime).Value = pRegistroIngresoCE.FechaDelIngreso
        cmd.Parameters.Add("@MontoCapital", SqlDbType.Float).Value = pRegistroIngresoCE.MontoCapital
        cmd.Parameters.Add("@MontoIngresoBruto", SqlDbType.Float).Value = pRegistroIngresoCE.MontoIngresoBruto
        cmd.Parameters.Add("@MontoISR", SqlDbType.Float).Value = pRegistroIngresoCE.MontoISR
        cmd.Parameters.Add("@MontoIngresoNeto", SqlDbType.Float).Value = pRegistroIngresoCE.MontoIngresoNeto
        cmd.Parameters.Add("@MontoIngresoLiquido", SqlDbType.Float).Value = pRegistroIngresoCE.MontoIngresoLiquido
        cmd.Parameters.Add("@TransId", SqlDbType.VarChar).Value = pRegistroIngresoCE.TransId

        cn = oConexion.Conectar
        cmd.Connection = cn
        cn.Open()
        retorno = cmd.ExecuteNonQuery

        'ACTUALIZA ESTADO DEL INGRESO PROYECTADO
        Dim vTipoIF As String
        query = String.Empty

        cmd = New SqlCommand
        cmd.CommandText = "SELECT T0.Tipo FROM [dbo].[INFI] T0 WHERE T0.CodIF = @CodIF"
        cmd.Parameters.Add("@CodIF", SqlDbType.VarChar).Value = pRegistroIngresoCE.CodigoInstrumento
        cmd.Connection = cn

        If Not cn.State = ConnectionState.Open Then
            cn.Open()
        End If

        vTipoIF = cmd.ExecuteScalar

        If vTipoIF = "ACC" Then
            query = "UPDATE [dbo].[ACC1] SET Estado='R' WHERE CodInv=@CodInv AND Correlativo=@Correlativo"
        End If
        If vTipoIF = "DAP" Then
            query = "UPDATE [dbo].[DAP1] SET Estado='R' WHERE CodInv=@CodInv AND Correlativo=@Correlativo"
        End If

        Dim vUpdate As Integer
        cmd = New SqlCommand
        cmd.CommandText = query
        cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = pRegistroIngresoCE.CodigoInversion
        cmd.Parameters.Add("@Correlativo", SqlDbType.VarChar).Value = pRegistroIngresoCE.IngresoID

        If Not cn.State = ConnectionState.Open Then
            cn.Open()
        End If
        cmd.Connection = cn
        vUpdate = cmd.ExecuteNonQuery

        If vUpdate <> 1 Then
            MsgBox("Falla al actualizar el Ingreso a 'Recibido'.")
        End If

        Return retorno
    End Function

    Public Function DetalleIngresoRecibido(ByVal pCodInv As String, ByVal pIngresoId As Integer) As RegistroIngresoCE
        Dim oRegistroIngresoCE As RegistroIngresoCE
        Dim oConexion As New Conexion
        Dim cn As SqlConnection
        Dim cmd As SqlCommand
        Dim query As String
        Dim vCount As Integer
        Dim vSqlParameter As SqlParameter
        Dim dr As SqlDataReader

        cn = oConexion.Conectar
        If Not cn.State = ConnectionState.Open Then
            cn.Open()
        End If

        cmd = New SqlCommand
        cmd.Connection = cn

        vSqlParameter = New SqlParameter
        vSqlParameter.ParameterName = "@CodInv"
        vSqlParameter.SqlDbType = SqlDbType.VarChar
        vSqlParameter.Direction = ParameterDirection.Input
        vSqlParameter.Value = pCodInv
        cmd.Parameters.Add(vSqlParameter)

        vSqlParameter = New SqlParameter
        vSqlParameter.ParameterName = "@IngresoId"
        vSqlParameter.SqlDbType = SqlDbType.VarChar
        vSqlParameter.Direction = ParameterDirection.Input
        vSqlParameter.Value = pIngresoId
        cmd.Parameters.Add(vSqlParameter)

        query = "SELECT COUNT('A')
FROM [dbo].[INGR] T0
WHERE T0.Codinv=@Codinv AND T0.IngresoId=@IngresoId"

        cmd.CommandText = query
        cmd.CommandType = CommandType.Text

        vCount = cmd.ExecuteScalar

        If vCount = 1 Then
            oRegistroIngresoCE = New RegistroIngresoCE

            query = "SELECT T0.*
FROM [dbo].[INGR] T0
WHERE T0.Codinv=@Codinv AND T0.IngresoId=@IngresoId"

            cmd.CommandText = query
            dr = cmd.ExecuteReader

            If dr.HasRows Then
                While dr.Read
                    oRegistroIngresoCE.CodigoBanco = dr.Item("CodBanco").ToString
                    oRegistroIngresoCE.FormaDePago = dr.Item("FormaPago").ToString
                    oRegistroIngresoCE.CuentaBancaria = dr.Item("CuentaBanco").ToString
                    oRegistroIngresoCE.FechaDelIngreso = dr.Item("FechaIngreso").ToString
                    oRegistroIngresoCE.Validado = dr.Item("Validado").ToString
                    If Not IsDBNull(dr.Item("FechaValidacion")) Then
                        oRegistroIngresoCE.FechaValidacion = dr.Item("FechaValidacion")
                    Else
                        oRegistroIngresoCE.FechaValidacion = Date.MinValue.Date
                    End If
                    oRegistroIngresoCE.Conciliado = dr.Item("Conciliado").ToString
                    If Not IsDBNull(dr.Item("FechaConciliacion")) Then
                        oRegistroIngresoCE.FechaConciliacion = dr.Item("FechaConciliacion")
                    Else
                        oRegistroIngresoCE.FechaConciliacion = Date.MinValue.Date
                    End If

                    oRegistroIngresoCE.MontoCapital = dr.Item("MontoCapital").ToString
                    oRegistroIngresoCE.MontoIngresoBruto = dr.Item("MontoIngresoBruto").ToString
                    oRegistroIngresoCE.MontoISR = dr.Item("MontoISR").ToString
                    oRegistroIngresoCE.MontoIngresoNeto = dr.Item("MontoIngresoNeto").ToString
                    oRegistroIngresoCE.MontoIngresoLiquido = dr.Item("MontoIngresoLiquido").ToString
                End While
            End If

        End If





        Return oRegistroIngresoCE
    End Function



End Module