Imports System.Data.OleDb
Public Class Inventory
    Private dbc As New OleDbConnection(conStr)
    Dim desc As String = ""


    Public Sub SearchProducts()
        Dim dba As New OleDbDataAdapter("SELECT * FROM tbProduct WHERE ProdName LIKE '%" & Me.TextBox2.Text & "%';", dbc)
        Dim dbset As New DataSet
        dba.Fill(dbset)
        Me.DataGridView2.DataSource = dbset.Tables(0).DefaultView

    End Sub

    Public Sub showProducts()
        Dim dba As New OleDbDataAdapter("SELECT * FROM tbProduct", dbc)
        Dim dbset As New DataSet
        dba.Fill(dbset)
        Me.DataGridView2.DataSource = dbset.Tables(0).DefaultView

    End Sub


    Public Sub showStocks()
        Dim dba As New OleDbDataAdapter("SELECT tbStocks.StockID,tbProduct.ProdName,tbStocks.Price,tbStocks.Quantity,tbStocks.ExpirationDate,tbStocks.Unit FROM (tbStocks INNER JOIN tbProduct ON tbStocks.ProdID=tbProduct.ProdID) WHERE tbStocks.ExpirationDate >=#" & Now() & "#", dbc)
        Dim dbset As New DataSet
        dba.Fill(dbset)
        Me.DataGridView3.DataSource = dbset.Tables(0).DefaultView

    End Sub

    Private Sub Form7_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        showProducts()
        showStocks()

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try

            For x As Integer = 0 To Me.DataGridView3.Rows.Count - 1
                If (Me.DataGridView2.CurrentRow.Cells(1).Value = Me.DataGridView3.Rows(x).Cells(1).Value) And (Me.TextBox1.Text = Me.DataGridView3.Rows(x).Cells(2).Value) Then
                    Dim upcmd As New OleDbCommand("UPDATE tbStocks SET Quantity=" & Me.DataGridView3.Rows(x).Cells(3).Value + Me.NumericUpDown1.Value & " WHERE StockID=" & Me.DataGridView3.Rows(x).Cells(0).Value & ";", dbc)
                    dbc.Open()
                    upcmd.ExecuteNonQuery()
                    dbc.Close()
                    showStocks()
                    Exit Sub
                End If

            Next
            If Me.NumericUpDown1.Value = 0 Then
                MessageBox.Show("The Quantity is 0.", "INVALID!", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Exit Sub
            End If
            If Me.TextBox3.Text = "" And Me.ComboBox1.Text = "" Then
                MessageBox.Show("The Unit is Empty", "INVALID!", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Exit Sub
            End If
            If Me.TextBox1.Text = "" Or Me.TextBox1.Text = "0" Then
                MessageBox.Show("The Price is Invalid", "INVALID!", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Exit Sub
            End If
            If Me.TextBox3.Text = "" Then
                MessageBox.Show("Unit is Empty", "INVALID!", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Exit Sub
            End If
         
            If MessageBox.Show("Add Stocks?", "ADD STOCKS", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) = DialogResult.No Then
                Exit Sub
            End If
            
            Dim incmd As New OleDbCommand("INSERT INTO tbStocks(ProdID,Quantity,Price,ExpirationDate, Unit) VALUES(" & Me.DataGridView2.CurrentRow.Cells(0).Value & "," & Me.NumericUpDown1.Value & "," & Me.TextBox1.Text & ",'" & Me.DateTimePicker1.Value & "','" & Me.TextBox3.Text + " " + desc & "')", dbc)
            dbc.Open()
            incmd.ExecuteNonQuery()
            dbc.Close()
            AdminForm.showStocks()
            showStocks()

            MessageBox.Show("Stocks was Successfully added", "ADD STOCKS", MessageBoxButtons.OK, MessageBoxIcon.Information)

        Catch ex As InvalidCastException
            MessageBox.Show("You have entered an invalid input in the price.", "INVALID!", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

    End Sub

    Public Sub reloadStocks()
        Dim dba As New OleDbDataAdapter("SELECT tbStocks.StockID,tbProduct.ProdName,tbStocks.Price,tbStocks.Quantity, tbStocks.Unit FROM tbStocks INNER JOIN tbProduct ON tbStocks.ProdID=tbProduct.ProdID;", dbc)
        Dim dbset As New DataSet
        dba.Fill(dbset)
        AdminForm.DataGridView1.DataSource = dbset.Tables(0).DefaultView


    End Sub

    Private Sub Form7_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing
        reloadStocks()

    End Sub

    Private Sub TextBox2_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox2.TextChanged
        SearchProducts()
    End Sub

    Private Sub GroupBox1_Enter(sender As Object, e As EventArgs) Handles GroupBox1.Enter

    End Sub

    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
      
        If Me.ComboBox1.SelectedIndex = 0 Then
            desc = "gram/s"
        End If
        If Me.ComboBox1.SelectedIndex = 1 Then
            desc = "Miligram/s"
        End If
        If Me.ComboBox1.SelectedIndex = 2 Then
            desc = "liter/s"
        End If
        If Me.ComboBox1.SelectedIndex = 3 Then
            desc = "mililiter/s"
        End If
        If Me.ComboBox1.SelectedIndex = 4 Then
            desc = "Pack"
        End If
        If Me.ComboBox1.SelectedIndex = 5 Then
            desc = "Sachet"
        End If
        If Me.ComboBox1.SelectedIndex = 6 Then
            desc = "Bottle"
        End If



    End Sub
    Public Sub searchinv()
        Dim dba As New OleDbDataAdapter("SELECT tbStocks.StockID,tbProduct.ProdName,tbStocks.Price,tbStocks.Quantity,tbStocks.ExpirationDate,tbStocks.Unit FROM (tbStocks INNER JOIN tbProduct ON tbStocks.ProdID=tbProduct.ProdID) WHERE tbStocks.ExpirationDate >=#" & Now() & "# AND tbProduct.ProdName LIKE '%" & Me.TextBox4.Text & "%'", dbc)
        Dim dbset As New DataSet
        dba.Fill(dbset)
        Me.DataGridView3.DataSource = dbset.Tables(0).DefaultView

    End Sub

    Private Sub TextBox4_TextChanged(sender As Object, e As EventArgs) Handles TextBox4.TextChanged
        searchinv()

    End Sub
End Class