'****************************************************************************************************************
'* Id Application modified by:   Date(MM/dd/yyyy)   Description													*
'* -- ------------------------   --------           -------------------------------------------	    			*
'* 01 Raśl Callupe - PACNET      22/10/2006         Creation of the class                                       *
'****************************************************************************************************************

Imports System
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.ApplicationBlocks.Data

Public Class CC_GrantType

    Sub New()

    End Sub

    Public Function GetGrantTypeByID(ByVal strGrantTypeID As String) As EC_GrantType
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_GRANTTYPEBYID"
        Dim arrParams(0) As SqlParameter
        Dim dreader As SqlClient.SqlDataReader
        Dim oEC_GrantType As New EC_GrantType

        Try
            arrParams(0) = New SqlParameter("@GRANTTYPEID", SqlDbType.Char, 3)
            arrParams(0).Value = strGrantTypeID
            dreader = SqlHelper.ExecuteReader(strConnectionString, CommandType.StoredProcedure, strSPName, arrParams)

            With oEC_GrantType
                While dreader.Read()
                    .GrantTypeID = IIf(dreader("GRANTTYPEID") Is DBNull.Value, String.Empty, dreader("GRANTTYPEID"))
                    .Description = IIf(dreader("DESCRIPTION") Is DBNull.Value, String.Empty, dreader("DESCRIPTION"))
                    .Threshold = IIf(dreader("THRESHOLD") Is DBNull.Value, String.Empty, dreader("THRESHOLD"))
                End While

            End With

        Catch ex As Exception
            Throw ex
        Finally
            If Not dreader Is Nothing Then
                dreader.Close()
            End If

        End Try

        Return oEC_GrantType
    End Function

    Public Function GetGrantTypes(ByVal nPage As Integer, ByVal nRecordsPerPage As Integer) As DataSet
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_GRANTTYPES"
        Dim arrParams(1) As SqlParameter
        Dim dsResult As DataSet

        arrParams(0) = New SqlParameter("@PAGE", SqlDbType.Int)
        arrParams(0).Value = nPage
        arrParams(1) = New SqlParameter("@RECORDSPERPAGE", SqlDbType.Int)
        arrParams(1).Value = nRecordsPerPage

        dsResult = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, strSPName, arrParams)

        Return dsResult
    End Function

    Public Function SaveGrantType(ByVal prmEC_GrantType As EC_GrantType, Optional ByRef strErrorMsg As String = "") As Boolean
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_SET_INSERT_GRANTTYPE"
        Dim arrParams(4) As SqlParameter
        Dim bSuccess As Boolean = False

        With prmEC_GrantType
            arrParams(0) = New SqlParameter("@GRANTTYPEID", SqlDbType.Char, 3)
            arrParams(0).Value = .GrantTypeID
            arrParams(1) = New SqlParameter("@DESCRIPTION", SqlDbType.VarChar, 50)
            arrParams(1).Value = .Description
            arrParams(2) = New SqlParameter("@THRESHOLD", SqlDbType.Int)

            If .Threshold = String.Empty Then
                arrParams(2).Value = DBNull.Value
            Else
                arrParams(2).Value = .Threshold
            End If

            arrParams(3) = New SqlParameter("@ADDEDBY", SqlDbType.VarChar, 15)
            arrParams(3).Value = .AddedBy
            arrParams(4) = New SqlParameter("@CHANGEDBY", SqlDbType.VarChar, 15)
            arrParams(4).Value = .ChangedBy
        End With

        Dim cnn As New SqlConnection(strConnectionString)
        cnn.Open()
        Dim tran As SqlTransaction = cnn.BeginTransaction()

        Try
            SqlHelper.ExecuteNonQuery(tran, CommandType.StoredProcedure, strSPName, arrParams)
            tran.Commit()
            bSuccess = True
        Catch ex As Exception
            tran.Rollback()
            bSuccess = False
            strErrorMsg = ex.Message
        Finally
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
        End Try

        Return bSuccess
    End Function

    Public Function UpdateGrantType(ByVal prmEC_GrantType As EC_GrantType, Optional ByRef strErrorMsg As String = "") As Boolean
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_SET_UPDATE_GRANTTYPE"
        Dim arrParams(3) As SqlParameter
        Dim bSuccess As Boolean = False

        With prmEC_GrantType
            arrParams(0) = New SqlParameter("@GRANTTYPEID", SqlDbType.Char, 3)
            arrParams(0).Value = .GrantTypeID
            arrParams(1) = New SqlParameter("@DESCRIPTION", SqlDbType.VarChar, 50)
            arrParams(1).Value = .Description
            arrParams(2) = New SqlParameter("@THRESHOLD", SqlDbType.Int)

            If .Threshold = String.Empty Then
                arrParams(2).Value = DBNull.Value
            Else
                arrParams(2).Value = .Threshold
            End If

            arrParams(3) = New SqlParameter("@CHANGEDBY", SqlDbType.VarChar, 15)
            arrParams(3).Value = .ChangedBy
        End With

        Dim cnn As New SqlConnection(strConnectionString)
        cnn.Open()
        Dim tran As SqlTransaction = cnn.BeginTransaction()

        Try
            SqlHelper.ExecuteNonQuery(tran, CommandType.StoredProcedure, strSPName, arrParams)
            tran.Commit()
            bSuccess = True
        Catch ex As Exception
            tran.Rollback()
            bSuccess = False
            strErrorMsg = ex.Message
        Finally
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
        End Try

        Return bSuccess
    End Function

    Public Function DeleteGrantOffice(ByVal prmEC_GrantType As EC_GrantType, Optional ByRef strErrorMsg As String = "") As Boolean
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_SET_DELETE_GRANTTYPE"
        Dim arrParams(1) As SqlParameter
        Dim bSuccess As Boolean = False

        With prmEC_GrantType
            arrParams(0) = New SqlParameter("@GRANTTYPEID", SqlDbType.Char, 3)
            arrParams(0).Value = .GrantTypeID
            arrParams(1) = New SqlParameter("@CHANGEDBY", SqlDbType.VarChar, 15)
            arrParams(1).Value = .ChangedBy
        End With

        Dim cnn As New SqlConnection(strConnectionString)
        cnn.Open()
        Dim tran As SqlTransaction = cnn.BeginTransaction()

        Try
            SqlHelper.ExecuteNonQuery(tran, CommandType.StoredProcedure, strSPName, arrParams)
            tran.Commit()
            bSuccess = True
        Catch ex As Exception
            tran.Rollback()
            bSuccess = False
            strErrorMsg = ex.Message
        Finally
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
        End Try

        Return bSuccess
    End Function

End Class

Public Class EC_GrantType
    Private _strGrantTypeId As String
    Private _strDescription As String = ""
    Private _strThreshold As String = ""
    Private _strAddedBy As String = ""
    Private _strChangedBy As String = ""

    Public Property GrantTypeID() As String
        Get
            Return _strGrantTypeId
        End Get
        Set(ByVal Value As String)
            _strGrantTypeId = Value
        End Set
    End Property

    Public Property Description() As String
        Get
            Return _strDescription
        End Get
        Set(ByVal Value As String)
            _strDescription = Value
        End Set
    End Property

    Public Property Threshold() As String
        Get
            Return _strThreshold
        End Get
        Set(ByVal Value As String)
            _strThreshold = Value
        End Set
    End Property

    Public Property AddedBy() As String
        Get
            Return _strAddedBy
        End Get
        Set(ByVal Value As String)
            _strAddedBy = Value
        End Set
    End Property

    Public Property ChangedBy() As String
        Get
            Return _strChangedBy
        End Get
        Set(ByVal Value As String)
            _strChangedBy = Value
        End Set
    End Property

End Class