360 lines
12 KiB
Plaintext
360 lines
12 KiB
Plaintext
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 _account As String
|
|
Private _usertype 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 account() As String
|
|
Get
|
|
Dim conn As New SqlConnection(cnstr)
|
|
|
|
Dim myQuery As String
|
|
myQuery = "SELECT account 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()
|
|
_account = reader("account")
|
|
End If
|
|
conn.Close()
|
|
Catch ex As Exception
|
|
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
|
|
conn.Close()
|
|
End Try
|
|
|
|
Return _account
|
|
End Get
|
|
Set(ByVal value As String)
|
|
_account = value
|
|
End Set
|
|
End Property
|
|
|
|
Public ReadOnly Property usertype() As String
|
|
Get
|
|
Dim conn As New SqlConnection(cnstr)
|
|
|
|
Dim myQuery As String
|
|
myQuery = "SELECT userType 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()
|
|
_usertype = reader("userType")
|
|
End If
|
|
conn.Close()
|
|
Catch ex As Exception
|
|
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
|
|
conn.Close()
|
|
End Try
|
|
|
|
Return _usertype
|
|
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 plant As String, ByVal account 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 & "'," & _
|
|
"'" & EncryptString(password) & "'," & _
|
|
"'" & plant & "'," & _
|
|
"'" & account & "'," & _
|
|
"'" & 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 plant As String, ByVal account 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 = " & "'" & EncryptString(password) & "'," & _
|
|
"plant = " & "'" & plant & "'," & _
|
|
"account = " & "'" & account & "'," & _
|
|
"userType = " & "'" & 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
|