Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports System.Security
Imports System.Security.Cryptography
Imports System.Text

Public Class User
    Private _username As String
    Private _password As String
    Private _plant As String
    Private _employeecode As String
    Private _authentication As String
    Private _active As Boolean
    Private cnstr As String
    Private connection As Connection

#Region "Properties"
    Public Property username() As String
        Get
            Return _username
        End Get
        Set(ByVal value As String)
            _username = value
        End Set
    End Property

    Public ReadOnly Property password() As String
        Get
            Dim conn As New SqlConnection(cnstr)

            Dim myQuery As String
            myQuery = "SELECT password FROM Users " & _
                      "WHERE username = " & "'" & _username & "'" & _
                      " AND active = 1"

            Dim mycommand As SqlCommand
            mycommand = New SqlCommand(myQuery, conn)

            Try
                conn.Open()
                Dim reader As SqlDataReader = mycommand.ExecuteReader()
                If (reader.HasRows) Then
                    reader.Read()
                    _password = reader("password")
                End If
                conn.Close()
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                conn.Close()
            End Try

            Return _password
        End Get
    End Property

    Public Property plant() As String
        Get
            Dim conn As New SqlConnection(cnstr)

            Dim myQuery As String
            myQuery = "SELECT plant FROM Users " & _
                      "WHERE username = " & "'" & _username & "'" & _
                      " AND active = 1"

            Dim mycommand As SqlCommand
            mycommand = New SqlCommand(myQuery, conn)

            Try
                conn.Open()
                Dim reader As SqlDataReader = mycommand.ExecuteReader()
                If (reader.HasRows) Then
                    reader.Read()
                    _plant = reader("plant")
                End If
                conn.Close()
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                conn.Close()
            End Try

            Return _plant
        End Get
        Set(ByVal value As String)
            _plant = value
        End Set
    End Property

    Public Property employeecode() As String
        Get
            Dim conn As New SqlConnection(cnstr)

            Dim myQuery As String
            myQuery = "SELECT employeecode FROM Users " & _
                      "WHERE username = " & "'" & _username & "'" & _
                      " AND active = 1"

            Dim mycommand As SqlCommand
            mycommand = New SqlCommand(myQuery, conn)

            Try
                conn.Open()
                Dim reader As SqlDataReader = mycommand.ExecuteReader()
                If (reader.HasRows) Then
                    reader.Read()
                    _employeecode = reader("employeecode")
                End If
                conn.Close()
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                conn.Close()
            End Try

            Return _employeecode
        End Get
        Set(ByVal value As String)
            _employeecode = value
        End Set
    End Property

    Public ReadOnly Property authentication() As String
        Get
            Dim conn As New SqlConnection(cnstr)

            Dim myQuery As String
            myQuery = "SELECT authentication FROM Users " & _
                      "WHERE username = " & "'" & _username & "'" & _
                      " AND active = 1"

            Dim mycommand As SqlCommand
            mycommand = New SqlCommand(myQuery, conn)

            Try
                conn.Open()
                Dim reader As SqlDataReader = mycommand.ExecuteReader()
                If (reader.HasRows) Then
                    reader.Read()
                    _authentication = reader("authentication")
                End If
                conn.Close()
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                conn.Close()
            End Try

            Return _authentication
        End Get
    End Property

    Public Property isActive() As Boolean
        Get
            Return _active
        End Get
        Set(ByVal value As Boolean)
            _active = value
        End Set
    End Property
#End Region

#Region "Methods"
    Public Sub New()
        cnstr = mainForm.cnstr
    End Sub

    Public Sub New(ByVal username As String)
        connection = New Connection
        connection.server = My.Settings.server
        connection.database = My.Settings.database
        connection.username = My.Settings.username
        connection.password = My.Settings.password
        connection.dbserver = My.Settings.dbserver
        connection.isTrusted = My.Settings.isTrusted
        cnstr = connection.cnStr
        mainForm.cnstr = cnstr
        _username = username
    End Sub

    Public Function EncryptString(ByVal sourceString As String) As String
        Dim sourceStringToBytes As Byte() = (New UnicodeEncoding()).GetBytes(sourceString)
        Dim hashedBytes As Byte() = New MD5CryptoServiceProvider().ComputeHash(sourceStringToBytes)
        Return BitConverter.ToString(hashedBytes)
    End Function

    Public Function check_user() As Boolean
        Dim conn As New SqlConnection(cnstr)

        Dim myQuery As String
        myQuery = "SELECT * FROM Users " & _
                  "WHERE username = " & "'" & _username & "'" & _
                  " AND active = 1"

        Dim mycommand As SqlCommand
        mycommand = New SqlCommand(myQuery, conn)

        Try
            conn.Open()
            Dim reader As SqlDataReader = mycommand.ExecuteReader()
            If (reader.HasRows) Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            conn.Close()
            conn.Dispose()
        End Try
    End Function

    Public Sub add(ByVal username As String, ByVal password As String, ByVal employeecode As String, ByVal type As String, ByVal isActive As Boolean)
        Dim conn As New SqlConnection(cnstr)
        Dim active As Integer = 0

        If isActive = True Then
            active = 1
        Else
            active = 0
        End If

        Dim myQuery As String
        myQuery = "INSERT INTO Users " & _
                  "VALUES(" & "'" & username & "'," & _
                  "'" & password & "'," & _
                  "'" & employeecode & "'," & _
                  "GETDATE()," & _
                  "'" & type & "'," & _
                  active & ")"

        Dim mycommand As SqlCommand
        mycommand = New SqlCommand(myQuery, conn)

        Try
            conn.Open()
            If (check_user() = False) Then
                mycommand.ExecuteNonQuery()
                MessageBox.Show("Successfully Saved.", "System User", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Else
                MessageBox.Show("User already exists in the database.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            conn.Close()
            conn.Dispose()
        End Try
    End Sub

    Public Sub edit(ByVal username As String, ByVal password As String, ByVal employeecode As String, ByVal type As String, ByVal isActive As Boolean)
        Dim conn As New SqlConnection(cnstr)
        Dim active As Integer = 0

        If isActive = True Then
            active = 1
        Else
            active = 0
        End If

        Dim myQuery As String
        myQuery = "UPDATE Users " & _
                  "SET username = " & "'" & username & "'," & _
                  "password = " & "'" & password & "'," & _
                  "employeecode = " & "'" & employeecode & "'," & _
                  "authentication = " & "'" & type & "'," & _
                  "active = " & active & _
                  " WHERE username = " & "'" & _username & "'"

        Dim mycommand As SqlCommand
        mycommand = New SqlCommand(myQuery, conn)

        Try
            conn.Open()
            mycommand.ExecuteNonQuery()
            MessageBox.Show("Successfully Saved.", "System User", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            conn.Close()
            conn.Dispose()
        End Try
    End Sub

    Public Sub delete(ByVal username As String)
        Dim conn As New SqlConnection(cnstr)

        Dim myQuery As String
        myQuery = "DELETE FROM Users " & _
                  "WHERE username = " & "'" & username & "'"

        Dim mycommand As SqlCommand
        mycommand = New SqlCommand(myQuery, conn)

        Try
            conn.Open()
            If (check_user() = True) Then
                mycommand.ExecuteNonQuery()
                MessageBox.Show("Successfully Deleted.", "System User", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Else
                MessageBox.Show("User does not exists in the database.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            conn.Close()
            conn.Dispose()
        End Try
    End Sub

    Public Function getPlants() As List(Of String)
        Dim plantList As New List(Of String)
        Dim conn As New SqlConnection(cnstr)

        Dim myQuery As String
        myQuery = "SELECT * FROM Plants ORDER BY plantName"

        Dim mycommand As SqlCommand
        mycommand = New SqlCommand(myQuery, conn)

        Try
            conn.Open()
            Dim reader As SqlDataReader = mycommand.ExecuteReader()
            While reader.Read
                plantList.Add(reader("plantName"))
            End While
            conn.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            conn.Close()
        End Try
        Return plantList
    End Function

    Public Function getAccounts() As List(Of String)
        Dim accountList As New List(Of String)
        Dim conn As New SqlConnection(cnstr)

        Dim myQuery As String
        myQuery = "SELECT * FROM Accounts ORDER BY accountName"

        Dim mycommand As SqlCommand
        mycommand = New SqlCommand(myQuery, conn)

        Try
            conn.Open()
            Dim reader As SqlDataReader = mycommand.ExecuteReader()
            While reader.Read
                accountList.Add(reader("accountName"))
            End While
            conn.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            conn.Close()
        End Try
        Return accountList
    End Function

#End Region
End Class