'****************************************************************************************************************
'* Id Application modified by:   Date(MM/dd/yyyy)   Description													*
'* -- ------------------------   --------           -------------------------------------------	    			*
'* 01 Raśl Callupe  - PACNET      03/22/2006         Creation of the class                                       *
'* 02 Raśl Callupe  - PACNET      03/22/2006         Creation of GetUsersByAccessGroup function     
'* 03 Vanessa Ayala - PACNET      03/27/2005         Creation of GetUserByLogin            
'****************************************************************************************************************

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

Public Class CC_User

    Sub New()

    End Sub

    Public Function GetManagersToNotify() As DataSet
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_NOTIFICATION_MANAGERS"
        Dim dsUsers As DataSet

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

        Return dsUsers
    End Function

    Public Function GetUsersByAccessGroup(ByVal prmAccessGroup As String) As DataSet
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_USERSBYACCESSGROUP"
        Dim arrParams(0) As SqlParameter
        Dim dsUsers As DataSet

        arrParams(0) = New SqlParameter("@ACCESSGROUP", SqlDbType.Char, 2)
        arrParams(0).Value = prmAccessGroup

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

        Return dsUsers

    End Function

    Public Function GetUserObjectByLogin(ByVal prmUserLogin As String) As EC_User
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_USERBYLOGIN"
        Dim arrParams(0) As SqlParameter
        Dim dreader As SqlClient.SqlDataReader
        Dim oEC_User As New EC_User

        Try
            arrParams(0) = New SqlParameter("@USERLOGIN", SqlDbType.VarChar, 15)
            arrParams(0).Value = prmUserLogin
            dreader = SqlHelper.ExecuteReader(strConnectionString, CommandType.StoredProcedure, strSPName, arrParams)

            With oEC_User
                While dreader.Read()
                    .UserID = IIf(dreader("USERID") Is DBNull.Value, 0, dreader("USERID"))
                    .Login = IIf(dreader("LOGIN") Is DBNull.Value, String.Empty, dreader("LOGIN"))
                    .Password = IIf(dreader("PASSWORD") Is DBNull.Value, String.Empty, dreader("PASSWORD"))
                    .Abbreviation = IIf(dreader("ABBREVIATION") Is DBNull.Value, String.Empty, dreader("ABBREVIATION"))
                    .AccessGroup = IIf(dreader("ACCESSGROUP") Is DBNull.Value, String.Empty, dreader("ACCESSGROUP"))
                    .Surname = IIf(dreader("SURNAME") Is DBNull.Value, String.Empty, dreader("SURNAME"))
                    .Name = IIf(dreader("NAME") Is DBNull.Value, String.Empty, dreader("NAME"))
                    .Email = IIf(dreader("EMAIL") Is DBNull.Value, String.Empty, dreader("EMAIL"))
                    .EmailNotif = IIf(dreader("EMAILNOTIF") Is DBNull.Value, String.Empty, dreader("EMAILNOTIF"))
                    .Recstat = IIf(dreader("RECSTAT") Is DBNull.Value, String.Empty, dreader("RECSTAT"))
                    .ConnectOutside = IIf(dreader("CONNECTOUTSIDE") Is DBNull.Value, String.Empty, dreader("CONNECTOUTSIDE"))
                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_User
    End Function

    Public Function GetUserByLogin(ByVal prmUserLogin As String) As DataSet
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_USERBYLOGIN"
        Dim arrParams(0) As SqlParameter
        Dim dsUsers As DataSet

        arrParams(0) = New SqlParameter("@USERLOGIN", SqlDbType.VarChar, 15)
        arrParams(0).Value = prmUserLogin

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

    End Function

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

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

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

        Return dsResult
    End Function

    Public Function GetUsers(ByVal nPage As Integer, ByVal nRecordsPerPage As Integer) As DataSet
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_USERS"
        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 GetUserByID(ByVal nUserID As Integer) As EC_User
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_USERBYID"
        Dim arrParams(0) As SqlParameter
        Dim dreader As SqlClient.SqlDataReader
        Dim oEC_User As New EC_User

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

            With oEC_User
                While dreader.Read()
                    .UserID = IIf(dreader("USERID") Is DBNull.Value, 0, dreader("USERID"))
                    .Surname = IIf(dreader("SURNAME") Is DBNull.Value, String.Empty, dreader("SURNAME"))
                    .Name = IIf(dreader("NAME") Is DBNull.Value, String.Empty, dreader("NAME"))
                    .Login = IIf(dreader("LOGIN") Is DBNull.Value, String.Empty, dreader("LOGIN"))
                    .Password = IIf(dreader("PASSWORD") Is DBNull.Value, String.Empty, dreader("PASSWORD"))
                    .Abbreviation = IIf(dreader("ABBREVIATION") Is DBNull.Value, 0, dreader("ABBREVIATION"))
                    .AccessGroup = IIf(dreader("ACCESSGROUP") Is DBNull.Value, String.Empty, dreader("ACCESSGROUP"))
                    .Email = IIf(dreader("EMAIL") Is DBNull.Value, String.Empty, dreader("EMAIL"))
                    .EmailNotif = IIf(dreader("EMAILNOTIF") Is DBNull.Value, String.Empty, dreader("EMAILNOTIF"))
                    .ConnectOutside = IIf(dreader("CONNECTOUTSIDE") Is DBNull.Value, String.Empty, dreader("CONNECTOUTSIDE"))
                    .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_User
    End Function

    Public Function SaveUser(ByVal prmUser As EC_User, Optional ByRef strErrorMsg As String = "") As Boolean
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_SET_INSERT_USER"
        Dim arrParams(10) As SqlParameter
        Dim bSuccess As Boolean = False

        With prmUser
            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("@ABBREVIATION", SqlDbType.Char, 2)
            arrParams(2).Value = .Abbreviation
            arrParams(3) = New SqlParameter("@ACCESSGROUP", SqlDbType.Char, 2)
            arrParams(3).Value = .AccessGroup
            arrParams(4) = New SqlParameter("@EMAIL", SqlDbType.VarChar, 50)
            arrParams(4).Value = .Email
            arrParams(5) = New SqlParameter("@EMAILNOTIF", SqlDbType.Char, 1)
            arrParams(5).Value = .EmailNotif
            arrParams(6) = New SqlParameter("@CONNECTOUTSIDE", SqlDbType.Char, 1)
            arrParams(6).Value = .ConnectOutside
            arrParams(7) = New SqlParameter("@LOGIN", SqlDbType.VarChar, 15)
            arrParams(7).Value = .Login
            arrParams(8) = New SqlParameter("@PASSWORD", SqlDbType.VarChar, 50)
            arrParams(8).Value = .Password
            arrParams(9) = New SqlParameter("@ADDEDBY", SqlDbType.VarChar, 15)
            arrParams(9).Value = .AddedBy
            arrParams(10) = New SqlParameter("@CHANGEDBY", SqlDbType.VarChar, 15)
            arrParams(10).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 UpdateUser(ByVal prmUser As EC_User, Optional ByRef strErrorMsg As String = "") As Boolean
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_SET_UPDATE_USER"
        Dim arrParams(10) As SqlParameter
        Dim bSuccess As Boolean = False

        With prmUser
            arrParams(0) = New SqlParameter("@USERID", SqlDbType.Int)
            arrParams(0).Value = .UserID
            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("@ABBREVIATION", SqlDbType.Char, 2)
            arrParams(3).Value = .Abbreviation
            arrParams(4) = New SqlParameter("@ACCESSGROUP", SqlDbType.Char, 2)
            arrParams(4).Value = .AccessGroup
            arrParams(5) = New SqlParameter("@EMAIL", SqlDbType.VarChar, 50)
            arrParams(5).Value = .Email
            arrParams(6) = New SqlParameter("@EMAILNOTIF", SqlDbType.Char, 1)
            arrParams(6).Value = .EmailNotif
            arrParams(7) = New SqlParameter("@CONNECTOUTSIDE", SqlDbType.Char, 1)
            arrParams(7).Value = .ConnectOutside
            arrParams(8) = New SqlParameter("@LOGIN", SqlDbType.VarChar, 15)
            arrParams(8).Value = .Login
            arrParams(9) = New SqlParameter("@PASSWORD", SqlDbType.VarChar, 50)
            arrParams(9).Value = .Password
            arrParams(10) = New SqlParameter("@CHANGEDBY", SqlDbType.VarChar, 15)
            arrParams(10).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 DeleteUser(ByVal prmUser As EC_User, Optional ByRef strErrorMsg As String = "") As Boolean
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_SET_DELETE_USER"
        Dim arrParams(1) As SqlParameter
        Dim bSuccess As Boolean = False

        With prmUser
            arrParams(0) = New SqlParameter("@USERID", SqlDbType.Int)
            arrParams(0).Value = .UserID
            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_User
    Private _nUserID As Integer
    Private _strLogin As String
    Private _strPassword As String
    Private _strAbbreviation As String
    Private _strAccessGroup As String
    Private _strSurname As String
    Private _strName As String
    Private _strEmail As String
    Private _strEmailNotif As String
    Private _strRecstat As String
    Private _strConnectOutside As String
    Private _strAddedBy As String
    Private _strChangedBy As String

    Public Property UserID() As Integer
        Get
            Return _nUserID
        End Get
        Set(ByVal Value As Integer)
            _nUserID = Value
        End Set
    End Property

    Public Property Login() As String
        Get
            Return _strLogin
        End Get
        Set(ByVal Value As String)
            _strLogin = Value
        End Set
    End Property

    Public Property Password() As String
        Get
            Return _strPassword
        End Get
        Set(ByVal Value As String)
            _strPassword = Value
        End Set
    End Property

    Public Property Abbreviation() As String
        Get
            Return _strAbbreviation
        End Get
        Set(ByVal Value As String)
            _strAbbreviation = Value
        End Set
    End Property

    Public Property AccessGroup() As String
        Get
            Return _strAccessGroup
        End Get
        Set(ByVal Value As String)
            _strAccessGroup = 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 Email() As String
        Get
            Return _strEmail
        End Get
        Set(ByVal Value As String)
            _strEmail = Value
        End Set
    End Property

    Public Property EmailNotif() As String
        Get
            Return _strEmailNotif
        End Get
        Set(ByVal Value As String)
            _strEmailNotif = Value
        End Set
    End Property

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

    Public Property ConnectOutside() As String
        Get
            Return _strConnectOutside
        End Get
        Set(ByVal Value As String)
            _strConnectOutside = Value
        End Set
    End Property

End Class