'****************************************************************************************************************
'* 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_Professional

    Sub New()

    End Sub

    Public Function GetDataForControls() As DataSet
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_PROFESSIONAL_CONTROLSDATA"
        Dim dsResult As DataSet

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

        dsResult.Tables(0).TableName = "PROFESSIONS"

        Return dsResult
    End Function

    Public Function GetProfessionals(ByVal nPage As Integer, _
                                        ByVal nRecordsPerPage As Integer, _
                                        Optional ByVal prmProfessionId As String = "") As DataSet
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_PROFESSIONALS"
        Dim arrParams(2) 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
        arrParams(2) = New SqlParameter("@PROFESSION", SqlDbType.VarChar, 2)
        arrParams(2).Value = prmProfessionId

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

        Return dsResult
    End Function

    Public Function GetProfessionalByID(ByVal nProfessionalID As Integer) As EC_Professional
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_PROFESSIONALBYID"
        Dim arrParams(0) As SqlParameter
        Dim dreader As SqlClient.SqlDataReader
        Dim oEC_Professional As New EC_Professional

        Try
            arrParams(0) = New SqlParameter("@PROFESSIONALID", SqlDbType.Int)
            arrParams(0).Value = nProfessionalID
            dreader = SqlHelper.ExecuteReader(strConnectionString, CommandType.StoredProcedure, strSPName, arrParams)

            With oEC_Professional
                While dreader.Read()
                    .ProfessionalID = IIf(dreader("PROFESSIONALID") Is DBNull.Value, 0, dreader("PROFESSIONALID"))
                    .Surname = IIf(dreader("SURNAME") Is DBNull.Value, 0, dreader("SURNAME"))
                    .Name = IIf(dreader("NAME") Is DBNull.Value, String.Empty, dreader("NAME"))
                    .Profession = IIf(dreader("PROFESSION") Is DBNull.Value, String.Empty, dreader("PROFESSION"))
                    .Telephone = IIf(dreader("TELEPHONE") Is DBNull.Value, String.Empty, dreader("TELEPHONE"))
                    .Address = IIf(dreader("ADDRESS") Is DBNull.Value, String.Empty, dreader("ADDRESS"))
                    .Postcode = IIf(dreader("POSTCODE") Is DBNull.Value, String.Empty, dreader("POSTCODE"))
                    .Email = IIf(dreader("EMAIL") Is DBNull.Value, String.Empty, dreader("EMAIL"))
                    .Mobile = IIf(dreader("MOBILE") Is DBNull.Value, String.Empty, dreader("MOBILE"))
                    .AddedBy = IIf(dreader("ADDEDBY") Is DBNull.Value, String.Empty, dreader("ADDEDBY"))
                    .ChangedBy = IIf(dreader("CHANGEDBY") Is DBNull.Value, String.Empty, dreader("CHANGEDBY"))
                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_Professional
    End Function

    Public Function SaveProfessional(ByVal prmProfessional As EC_Professional, Optional ByRef strErrorMsg As String = "") As Boolean
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_SET_INSERT_PROFESSIONAL"
        Dim arrParams(9) As SqlParameter
        Dim bSuccess As Boolean = False

        With prmProfessional
            arrParams(0) = New SqlParameter("@SURNAME", SqlDbType.VarChar, 50)
            arrParams(0).Value = .Surname
            arrParams(1) = New SqlParameter("@NAME", SqlDbType.VarChar, 50)
            arrParams(1).Value = .Name
            arrParams(2) = New SqlParameter("@PROFESSION", SqlDbType.Char, 2)
            arrParams(2).Value = .Profession
            arrParams(3) = New SqlParameter("@TELEPHONE", SqlDbType.VarChar, 12)
            arrParams(3).Value = .Telephone
            arrParams(4) = New SqlParameter("@ADDRESS", SqlDbType.VarChar, 100)
            arrParams(4).Value = .Address
            arrParams(5) = New SqlParameter("@POSTCODE", SqlDbType.Char, 7)
            arrParams(5).Value = .Postcode
            arrParams(6) = New SqlParameter("@EMAIL", SqlDbType.VarChar, 50)
            arrParams(6).Value = .Email
            arrParams(7) = New SqlParameter("@MOBILE", SqlDbType.VarChar, 12)
            arrParams(7).Value = .Mobile
            arrParams(8) = New SqlParameter("@ADDEDBY", SqlDbType.VarChar, 15)
            arrParams(8).Value = .AddedBy
            arrParams(9) = New SqlParameter("@CHANGEDBY", SqlDbType.VarChar, 15)
            arrParams(9).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 UpdateProfessional(ByVal prmProfessional As EC_Professional, Optional ByRef strErrorMsg As String = "") As Boolean
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_SET_UPDATE_PROFESSIONAL"
        Dim arrParams(9) As SqlParameter
        Dim bSuccess As Boolean = False

        With prmProfessional
            arrParams(0) = New SqlParameter("@PROFESSIONALID", SqlDbType.Int)
            arrParams(0).Value = .ProfessionalID
            arrParams(1) = New SqlParameter("@SURNAME", SqlDbType.VarChar, 50)
            arrParams(1).Value = .Surname
            arrParams(2) = New SqlParameter("@NAME", SqlDbType.VarChar, 50)
            arrParams(2).Value = .Name
            arrParams(3) = New SqlParameter("@PROFESSION", SqlDbType.Char, 2)
            arrParams(3).Value = .Profession
            arrParams(4) = New SqlParameter("@TELEPHONE", SqlDbType.VarChar, 12)
            arrParams(4).Value = .Telephone
            arrParams(5) = New SqlParameter("@ADDRESS", SqlDbType.VarChar, 100)
            arrParams(5).Value = .Address
            arrParams(6) = New SqlParameter("@POSTCODE", SqlDbType.Char, 7)
            arrParams(6).Value = .Postcode
            arrParams(7) = New SqlParameter("@EMAIL", SqlDbType.VarChar, 50)
            arrParams(7).Value = .Email
            arrParams(8) = New SqlParameter("@MOBILE", SqlDbType.VarChar, 12)
            arrParams(8).Value = .Mobile
            arrParams(9) = New SqlParameter("@CHANGEDBY", SqlDbType.VarChar, 15)
            arrParams(9).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 DeleteProfessional(ByVal prmProfessional As EC_Professional, Optional ByRef strErrorMsg As String = "") As Boolean
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_SET_DELETE_PROFESSIONAL"
        Dim arrParams(1) As SqlParameter
        Dim bSuccess As Boolean = False

        With prmProfessional
            arrParams(0) = New SqlParameter("@PROFESSIONALID", SqlDbType.Int)
            arrParams(0).Value = .ProfessionalID
            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_Professional
    Private _nProfessionalID As Integer
    Private _strSurname As String = ""
    Private _strName As String = ""
    Private _strProfession As String = ""
    Private _strTelephone As String = ""
    Private _strAddress As String = ""
    Private _strPostcode As String = ""
    Private _strEmail As String = ""
    Private _strMobile As String = ""
    Private _strAddedBy As String = ""
    Private _strChangedBy As String = ""

    Public Property ProfessionalID() As Integer
        Get
            Return _nProfessionalID
        End Get
        Set(ByVal Value As Integer)
            _nProfessionalID = Value
        End Set
    End Property

    Public Property Surname() As String
        Get
            Return _strSurname
        End Get
        Set(ByVal Value As String)
            _strSurname = Value
        End Set
    End Property

    Public Property Name() As String
        Get
            Return _strName
        End Get
        Set(ByVal Value As String)
            _strName = Value
        End Set
    End Property

    Public Property Profession() As String
        Get
            Return _strProfession
        End Get
        Set(ByVal Value As String)
            _strProfession = Value
        End Set
    End Property

    Public Property Telephone() As String
        Get
            Return _strTelephone
        End Get
        Set(ByVal Value As String)
            _strTelephone = Value
        End Set
    End Property

    Public Property Address() As String
        Get
            Return _strAddress
        End Get
        Set(ByVal Value As String)
            _strAddress = Value
        End Set
    End Property

    Public Property Postcode() As String
        Get
            Return _strPostcode
        End Get
        Set(ByVal Value As String)
            _strPostcode = Value
        End Set
    End Property

    Public Property Email() As String
        Get
            Return _strEmail
        End Get
        Set(ByVal Value As String)
            _strEmail = Value
        End Set
    End Property

    Public Property Mobile() As String
        Get
            Return _strMobile
        End Get
        Set(ByVal Value As String)
            _strMobile = 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