Imports System.Data.OleDb

Public Class Ordering
    Dim dbc As New OleDbConnection(conStr)

    Public Sub SearchStocks()
        Dim dba As New OleDbDataAdapter("SELECT tbStocks.StockID,tbProduct.ProdName,tbStocks.Price,tbStocks.Quantity FROM tbStocks INNER JOIN tbProduct ON tbStocks.ProdID=tbProduct.ProdID WHERE tbProduct.ProdName LIKE '%" & Me.TextBox3.Text & "%';", dbc)
        Dim dbset As New DataSet
        dba.Fill(dbset)
        Me.DataGridView1.DataSource = dbset.Tables(0).DefaultView

    End Sub

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

    End Sub

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

    Private Sub SoldProducts()
        Dim dba As New OleDbDataAdapter("SELECT ProdName,Price,Quantity,ProdID FROM tbProductSold", dbc)
        Dim dbset As New DataSet
        dba.Fill(dbset)
        frmMain.customersListdgv.DataSource = dbset.Tables(0).DefaultView
    End Sub

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

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub


    Private Sub Stocks()
        Dim dba As New OleDbDataAdapter("SELECT tbStocks.StockID,tbProduct.ProdName,tbStocks.Price,tbStocks.Quantity FROM tbStocks INNER JOIN tbProduct ON tbStocks.ProdID=tbProduct.ProdID;", dbc)
        Dim dbset As New DataSet
        dba.Fill(dbset)
        frmMain.DataGridView2.DataSource = dbset.Tables(0).DefaultView

    End Sub

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

    End Sub

    Private Sub NumericUpDown1_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.NumericUpDown1.Minimum = 1
        Me.NumericUpDown1.Maximum = Me.DataGridView1.CurrentRow.Cells(3).Value
    End Sub

    Private Sub TextBox3_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox3.TextChanged
        SearchStocks()

    End Sub

    Private Sub GroupBox2_Enter(sender As Object, e As EventArgs)

    End Sub

    Private Sub Button1_Click_1(sender As Object, e As EventArgs) Handles Button1.Click
        If Me.NumericUpDown1.Value = 0 Or Me.NumericUpDown1.Value > Me.DataGridView1.CurrentRow.Cells(3).Value Then
            MessageBox.Show("The Quantity you have Entered Might be 0 or beyond the Quantity Limit. ", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End If

        If Me.NumericUpDown1.Value > 0 Or Me.NumericUpDown1.Value <= Me.DataGridView1.CurrentRow.Cells(2).Value Then

            For x As Integer = 0 To frmMain.customersListdgv.Rows.Count - 1

                If Me.DataGridView1.CurrentRow.Cells(0).Value = frmMain.customersListdgv.Rows(x).Cells(3).Value Then
                    Dim upcmdForm1 As New OleDbCommand("UPDATE tbProductSold SET Price=" & (Me.NumericUpDown1.Value + frmMain.customersListdgv.Rows(x).Cells(2).Value) * Me.DataGridView1.CurrentRow.Cells(2).Value & ",Quantity=" & Me.NumericUpDown1.Value + frmMain.customersListdgv.Rows(x).Cells(2).Value & " WHERE ProdID=" & Me.DataGridView1.CurrentRow.Cells(0).Value & ";", dbc)
                    dbc.Open()
                    upcmdForm1.ExecuteNonQuery()
                    dbc.Close()
                    SoldProducts()

                    Dim Update As New OleDbCommand("UPDATE tbStocks SET Quantity=" & Me.DataGridView1.CurrentRow.Cells(3).Value - Me.NumericUpDown1.Value & " WHERE StockID=" & Me.DataGridView1.CurrentRow.Cells(0).Value & ";", dbc)
                    dbc.Open()
                    Update.ExecuteNonQuery()
                    dbc.Close()
                    showStocks()


                    Dim dba1 As New OleDbDataAdapter("SELECT SUM(Price) FROM tbProductSold", dbc)
                    Dim dbset1 As New DataSet
                    dba1.Fill(dbset1)
                    frmMain.tbill.Text = dbset1.Tables(0).DefaultView.Item(0).Item(0)

                    MessageBox.Show("Successfully Added to Order List!!", "", MessageBoxButtons.OK, MessageBoxIcon.Information)


                    Exit Sub

                End If


            Next

            Dim incmd As New OleDbCommand("INSERT INTO tbProductSold(ProdID,ProdName,Price,Quantity) VALUES(" & Me.DataGridView1.CurrentRow.Cells(0).Value & ",'" & Me.DataGridView1.CurrentRow.Cells(1).Value & "'," & Me.DataGridView1.CurrentRow.Cells(2).Value * Me.NumericUpDown1.Value & "," & Me.NumericUpDown1.Value & ")", dbc)
            dbc.Open()
            incmd.ExecuteNonQuery()
            dbc.Close()
            'ShowProductSold()

            Dim upcmd As New OleDbCommand("UPDATE tbStocks SET Quantity=" & Me.DataGridView1.CurrentRow.Cells(3).Value - Me.NumericUpDown1.Value & " WHERE StockID=" & Me.DataGridView1.CurrentRow.Cells(0).Value & ";", dbc)
            dbc.Open()
            upcmd.ExecuteNonQuery()
            dbc.Close()
            showStocks()
            SoldProducts()


            Dim dba As New OleDbDataAdapter("SELECT SUM(Price) FROM tbProductSold", dbc)
            Dim dbset As New DataSet
            dba.Fill(dbset)
            frmMain.tbill.Text = dbset.Tables(0).DefaultView.Item(0).Item(0)

            MessageBox.Show("The Product is Successfully Added to the List of Product Sold", "SOLD PRODUCT", MessageBoxButtons.OK, MessageBoxIcon.Information)

        Else
            MessageBox.Show("The Quantity you have Entered is invalid. ", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)


        End If
    End Sub

End Class