Inversiones/DAO/RequisicionDePagoDAO.vb

Module RequisicionDePagoDAO
    Dim Operaciones As New Operaciones
    Public Function ExisteRequisicion(ByVal pCodigoInversion As String) As Boolean
        Dim retorno As Boolean = False
        Dim resultado As Integer
        Dim query As String
        Dim cmd As SqlClient.SqlCommand
        Dim cn As SqlClient.SqlConnection
        Dim oConexion = New Conexion

        If String.IsNullOrEmpty(pCodigoInversion) Then
            GoTo Salida
        End If

        query = "SELECT COUNT('A') FROM [dbo].[RPAG] T0 WHERE T0.[CodInv]=@CodInv"
        cmd = New SqlClient.SqlCommand
        cmd.CommandText = query
        cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = pCodigoInversion

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

        cmd.Connection = cn
        resultado = cmd.ExecuteScalar
        If resultado = 1 Then retorno = True
Salida:
        Return retorno
    End Function
    Public Function CargarRegistro(ByVal pCodigoInversion As String) As RequisicionDePagoCE
        Dim oRequisicionDePagoCE As New RequisicionDePagoCE
        Dim cn As SqlClient.SqlConnection
        Dim cmd As SqlClient.SqlCommand
        Dim query As String
        Dim dr As SqlClient.SqlDataReader
        Dim oConexion As New Conexion

        If ExisteRequisicion(pCodigoInversion) Then
            oRequisicionDePagoCE = New RequisicionDePagoCE
            query = "SELECT * FROM [dbo].[RPAG] T0  WHERE T0.[CodInv]=@CodInv"
            cmd = New SqlClient.SqlCommand
            cmd.CommandText = query
            cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = pCodigoInversion

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

            cmd.Connection = cn
            dr = cmd.ExecuteReader

            If dr.HasRows Then
                While dr.Read
                    oRequisicionDePagoCE.CodigoInversion = dr.Item("CodInv").ToString
                    oRequisicionDePagoCE.CodigoReq = dr.Item("CodReq").ToString
                    oRequisicionDePagoCE.UsuarioSolicitante = dr.Item("UCodigo").ToString
                    oRequisicionDePagoCE.AFavorDe = dr.Item("AFavorDe").ToString
                    oRequisicionDePagoCE.FormaDePago = dr.Item("FormaPagID").ToString
                    oRequisicionDePagoCE.MontoDelPago = dr.Item("MontoPago").ToString
                    oRequisicionDePagoCE.FechaDelPago = dr.Item("FechaPago").ToString
                    oRequisicionDePagoCE.CodigoBanco = dr.Item("CodBanco")
                    oRequisicionDePagoCE.NumeroCuenta = dr.Item("NumCuenta").ToString
                    oRequisicionDePagoCE.Concepto = dr.Item("Concepto").ToString
                    oRequisicionDePagoCE.SaldoContable = dr.Item("SaldoContable").ToString
                End While
            End If
        End If


        If String.IsNullOrEmpty(pCodigoInversion) Then
            GoTo Salida
        End If
Salida:
        Return oRequisicionDePagoCE
    End Function

    Public Function GuardarNuevaRequisicion(ByVal pRequisicionDePagoCE As RequisicionDePagoCE, ByVal TipoOperacion As String) As Integer
        Dim query As String
        Dim cmd As SqlClient.SqlCommand
        Dim cn As SqlClient.SqlConnection
        Dim oConexion As New Conexion
        Dim retorno As Integer



        If TipoOperacion = "N" Then
            query = "INSERT INTO [dbo].[RPAG]
	([UCodigo]
	,[CodInv]
    ,[CodReq]
	,[AFavorDe]
	,[FormaPagID]
	,[MontoPago]
	,[FechaPago]
	,[CodBanco]
	,[NumCuenta]
	,[SaldoContable]
	,[Concepto])
VALUES
	(@UCodigo
	,@CodInv
    ,@CodReq
	,@AFavorDe
	,@FormaPagID
	,@MontoPago
	,@FechaPago
	,@CodBanco
	,@NumCuenta
	,@SaldoContable
	,@Concepto
)"

        Else


            query = "UPDATE [dbo].[RPAG] SET
	[UCodigo]=	@UCodigo
    ,[CodReq]=	@CodReq
	,[AFavorDe]=@AFavorDe
	,[FormaPagID]=@FormaPagID
	,[MontoPago]=@MontoPago
	,[FechaPago]=@FechaPago
	,[CodBanco]=@CodBanco
	,[NumCuenta]=@NumCuenta
	,[SaldoContable]=@SaldoContable
	,[Concepto]=@Concepto
    WHERE [CodInv]=@CodInv
"
        End If

        cmd = New SqlClient.SqlCommand
        cmd.CommandText = query
        cmd.Parameters.Add("@UCodigo", SqlDbType.VarChar).Value = pRequisicionDePagoCE.UsuarioSolicitante
        cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = pRequisicionDePagoCE.CodigoInversion
        cmd.Parameters.Add("@AFavorDe", SqlDbType.VarChar).Value = pRequisicionDePagoCE.AFavorDe
        cmd.Parameters.Add("@FormaPagID", SqlDbType.VarChar).Value = pRequisicionDePagoCE.FormaDePago
        cmd.Parameters.Add("@MontoPago", SqlDbType.Float).Value = pRequisicionDePagoCE.MontoDelPago
        cmd.Parameters.Add("@FechaPago", SqlDbType.DateTime).Value = pRequisicionDePagoCE.FechaDelPago
        cmd.Parameters.Add("@CodBanco", SqlDbType.VarChar).Value = pRequisicionDePagoCE.CodigoBanco
        cmd.Parameters.Add("@NumCuenta", SqlDbType.VarChar).Value = pRequisicionDePagoCE.NumeroCuenta
        cmd.Parameters.Add("@SaldoContable", SqlDbType.Float).Value = pRequisicionDePagoCE.SaldoContable
        cmd.Parameters.Add("@Concepto", SqlDbType.VarChar).Value = pRequisicionDePagoCE.Concepto
        cmd.Parameters.Add("@CodReq", SqlDbType.VarChar).Value = pRequisicionDePagoCE.CodigoReq
        cn = oConexion.Conectar
        If Not cn.State = ConnectionState.Open Then
            cn.Open()
        End If

        cmd.Connection = cn
        retorno = cmd.ExecuteNonQuery



Salida:
        Return retorno
    End Function


    Function NuevoCodigo()
        Dim objCon As New Conexion
        Dim valor As String = String.Empty
        Dim cn As SqlClient.SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If
        Dim sql = "[dbo].[SP_CodigoNuevoReq]"
        Dim cmd As New SqlClient.SqlCommand(sql, cn)
        cmd.CommandType = CommandType.StoredProcedure
        valor = cmd.ExecuteScalar
        cmd.Dispose()
        cn.Dispose()
        Return valor
    End Function



    Function Monto(ByVal CodigoInversion As String)
        Dim objCon As New Conexion
        Dim valor As Double = 0
        Dim cn As SqlClient.SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If
        Dim sql = "[dbo].[SP_MontoTituloUnico]"
        Dim cmd As New SqlClient.SqlCommand(sql, cn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("@CodigoInversion", SqlDbType.VarChar).Value = CodigoInversion
        valor = Operaciones.ConvertirDecimal(cmd.ExecuteScalar)
        cmd.Dispose()
        cn.Dispose()
        Return valor
    End Function
End Module