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


Public Class Plant
    Private cnstr As String
    Private _plantCode As String
    Private _plantName As String
    Private _createdby As String
    Private _createddate As DateTime
    Dim myquery As String


#Region "Property"
    Public Property plantCode() As String
        Get
            Return _plantCode
        End Get
        Set(ByVal value As String)
            _plantCode = value
        End Set
    End Property

    Public Property plantName() As String
        Get
            Return _plantName
        End Get
        Set(ByVal value As String)
            _plantName = value
        End Set
    End Property

    Public Property createdby() As String
        Get
            Return _createdby
        End Get
        Set(ByVal value As String)
            _createdby = value
        End Set
    End Property

    Public Property createddate() As DateTime
        Get
            Return (_createddate)
        End Get
        Set(ByVal value As DateTime)
            _createddate = value
        End Set
    End Property

#End Region



    Public Sub getplant()
        Dim conn As New SqlConnection(cnstr)
        Dim myquery As String

        myquery = "SELECT * FROM KANBAN.DBO.PLANTS"

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

        Try
            conn.Open()
            Dim reader As SqlDataReader = mycommand.ExecuteReader()
            If (reader.HasRows) Then
                reader.Read()
                plantCode = reader("plantCode")
                plantName = reader("plantName")
                createdby = reader("createdby")
                createddate = reader("createddate")
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
        conn.Close()
        conn.Dispose()
    End Sub

    Public Function retrievePlant() As List(Of String)
        Dim plantlist As New List(Of String)
        Dim conn As New SqlConnection(cnstr)
        Dim myquery As String

        myquery = "SELECT * FROM KANBAN.DBO.PLANTS"

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

        Try
            conn.Open()
            Dim reader As SqlDataReader = mycommand.ExecuteReader()
            While reader.Read
                plantlist.Add(reader("plantCode"))
            End While
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            conn.Close()
            conn.Dispose()
        End Try

        Return plantlist
    End Function

    Public Sub AddRecordPlant()
        Dim conn As New SqlConnection(cnstr)

        myquery = "INSERT INTO PLANTS (plantCode, " & _
            "plantName, createdby, createddate) " & _
            "VALUES ('" & plantCode & "', " & _
            "'" & createdby & "', " & _
            "'" & createddate & "')"

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

        Try
            conn.Open()
            If (checkExistplant(plantCode) = False) Then
                mycommand.ExecuteNonQuery()
            Else
                MessageBox.Show("Plant 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 UpdateRecordPlant(ByVal oldPlant As String)
        Dim conn As New SqlConnection(cnstr)

        myquery = "Update PLANTS " & _
            "SET plantCode = " & "'" & plantCode & "'" & "," & _
            "plantName = '" & plantName & "'" & "," & _
            "createdby = '" & createdby & "'" & "" & _
            "createddate = '" & createddate & "'" & "" & _
            "WHERE plantCode = '" & oldPlant & "'"

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

        Try
            conn.Open()
            If (checkExistplant(plantCode) = False) Then
                mycommand.ExecuteNonQuery()
            Else
                MessageBox.Show("Plant 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 deleteRecordPlant(ByVal plantCode As String)
        Dim conn As New SqlConnection(cnstr)

        myquery = "DELETE FROM PLANTS" & _
            "where plantCode = '" & plantCode & "'"

        Dim mycommand As SqlCommand
        mycommand = New SqlCommand(myquery, Conn)

        Try
            conn.Open()
            If (checkExistplant(plantCode) = True) Then
                mycommand.ExecuteNonQuery()
            Else
                MessageBox.Show("Plant 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 checkExistplant(ByVal plantCode As String) As Boolean
        Dim conn As New SqlConnection(cnstr)
        Dim result As Boolean = False

        myquery = "SELECT * FROM PLANTS " & _
            "WHERE plantCode = " & "'" & plantCode & "'"

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

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