Imports System.Data.OleDb
Public Class frmMain
    Dim dbc As New OleDbConnection(conStr)


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


   

    Public Sub showStocksw()
        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)
        Me.DataGridView2.DataSource = dbset.Tables(0).DefaultView

    End Sub


    Private Sub SoldProducts()
        Dim dba As New OleDbDataAdapter("SELECT ProdName,Price as [Total Price],Quantity,ProdID,Unit FROM tbProductSold", dbc)
        Dim dbset As New DataSet
        dba.Fill(dbset)
        Me.customersListdgv.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.Unit FROM tbStocks INNER JOIN tbProduct ON tbStocks.ProdID=tbProduct.ProdID WHERE tbStocks.Quantity>0;", dbc)
        Dim dbset As New DataSet
        dba.Fill(dbset)
        Me.productsdgv.DataSource = dbset.Tables(0).DefaultView

    End Sub
    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.TextBox8.Text & "%';", dbc)
        Dim dbset As New DataSet
        dba.Fill(dbset)
        Me.productsdgv.DataSource = dbset.Tables(0).DefaultView

    End Sub

    Private Sub orgen()
        Dim dlcmd As New OleDbCommand("INSERT INTO tbOR(Time) VALUES(#" & Now().Date & "#)", dbc)
        dbc.Open()
        dlcmd.ExecuteNonQuery()
        dbc.Close()

        Dim dba As New OleDbDataAdapter("SELECT ORNum FROM tbOR;", dbc)
        Dim dbset As New DataSet
        dba.Fill(dbset)
        Me.TextBox1.Text = "1512" & dbset.Tables(0).DefaultView.Item(0).Item(0)

    End Sub



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

        showStocks()
        SoldProducts()

        ' Me.NumericUpDown1.Visible = False
        Me.ToolStripStatusLabel1.Text = Now
        Me.Timer1.Start()
        ' Me.Button4.Visible = False
        Me.Label9.Visible = False
        Me.amount.Visible = False
        Me.Button3.Visible = False
        Me.DataGridView2.Visible = False
        Me.Button5.Visible = False

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Ordering.TextBox1.Text = Me.TextBox2.Text
        Ordering.Show()

    End Sub

    Private Sub DataGridView1_SelectionChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles customersListdgv.SelectionChanged

        Me.TextBox5.Text = Me.customersListdgv.CurrentRow.Cells(1).Value / Me.customersListdgv.CurrentRow.Cells(2).Value
        Me.TextBox7.Text = Me.customersListdgv.CurrentRow.Cells(2).Value
        Me.TextBox9.Text = Me.customersListdgv.CurrentRow.Cells(4).Value


        If Me.customersListdgv.CurrentRow.Cells(2).Value = 1 Then
            '  Me.Button4.Text = "DELETE TRANSACTION"
            ' Me.Button4.Visible = True
            '  Me.NumericUpDown1.Visible = False
            Me.Button5.Visible = False
        End If

        If Me.customersListdgv.CurrentRow.Cells(2).Value > 1 Then
            'Me.Button4.Text = "UPDATE TRANSACTION"
            'Me.Button4.Visible = True
            ' Me.NumericUpDown1.Visible = True
            Me.Button5.Visible = True
        End If
    End Sub

    Private Sub Form1_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing
        Dim dlcmd As New OleDbCommand("DELETE * FROM tbProductSold", dbc)
        dbc.Open()
        dlcmd.ExecuteNonQuery()
        dbc.Close()

        Login.Show()
        Me.Hide()


    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click


        If Me.Button2.Text = "SAVE" Then
            Me.Button5.Visible = True
            Me.Button2.Text = "PAY"
            Me.Label9.Visible = True
            Me.amount.Visible = True
            Me.Button3.Visible = True
            If Me.tbill.Text = "" Then
                MessageBox.Show("There's No Customer's Order", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Exit Sub
            End If
        ElseIf Me.Button2.Text = "PAY" Then
            Dim changee As Integer = Me.amount.Text - Me.tbill.Text

            If changee < 0 Then
                MessageBox.Show("The Payment is Insufficient", "INSUFFICIENT PAYMENT", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Sub
            End If

            If MessageBox.Show("Finalize Ordering", "Are you sure?", MessageBoxButtons.YesNo, MessageBoxIcon.Information, MessageBoxDefaultButton.Button2) = DialogResult.No Then
                Me.Button2.Text = "SAVE"
                Me.Label9.Visible = False
                Me.amount.Visible = False
                Me.Button3.Visible = False
                Me.Button5.Visible = False

                Exit Sub
            Else

                If changee >= 0 Then

                    Me.change.Text = changee
                    'Me.TextBox2.Text = ""
                    'Me.tbill.Text = ""
                    'Me.amount.Text = ""
                    'Me.TextBox1.Text = ""

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


                    For x As Integer = 0 To Me.customersListdgv.Rows.Count - 1
                        Dim soldcmd As New OleDbCommand("INSERT INTO tbSold (ORNum, ClientName, ProdID, Unit, Quantity, Price, TotBill, Payment, Change, TransactionDate) VALUES(" & Me.TextBox1.Text & ",'" & Me.TextBox2.Text & "'," & customersListdgv.Item(3, x).Value & ", '" & customersListdgv.Item(4, x).Value & "'," & customersListdgv.Item(2, x).Value & "," & customersListdgv.Item(1, x).Value & ",'" & Me.tbill.Text & "','" & Me.amount.Text & "'," & Me.change.Text & ", #" & Now() & "#)", dbc)
                        dbc.Open()
                        soldcmd.ExecuteNonQuery()
                        dbc.Close()
                    Next
                    Dim insrtslecmd As New OleDbCommand("INSERT INTO tbSale (SaleDate, ORNum, CustomerName, TotalBill, Amount) VALUES (#" & Now() & "#, " & Me.TextBox1.Text & ",'" & Me.TextBox2.Text & "', '" & Me.tbill.Text & "', '" & Me.amount.Text & "')", dbc)
                    dbc.Open()
                    insrtslecmd.ExecuteNonQuery()
                    dbc.Close()
                    Me.TextBox2.Text = ""
                    Me.TextBox5.Text = ""
                    Me.TextBox7.Text = ""
                    Me.TextBox9.Text = ""
                    Me.TextBox1.Text = ""
                    Me.amount.Text = ""
                    Me.tbill.Text = ""
                    Me.NumericUpDown1.Value = 0
                    Dim dlcmd As New OleDbCommand("DELETE * FROM tbProductSold", dbc)
                    dbc.Open()
                    dlcmd.ExecuteNonQuery()
                    dbc.Close()

                    SoldProducts()
                    ' MessageBox.Show("Thank you Very Much. Your change is " & changee & ".", "Thank you", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    Exit Sub

                End If

            End If


            Try

                Dim incmd As New OleDbCommand("INSERT INTO tbSale(SaleDate,CustomerName,TotalBill,Amount) VALUES(#" & Me.ToolStripStatusLabel1.Text & "#,'" & Me.TextBox2.Text & "','" & Me.tbill.Text & "," & Me.amount.Text & ")", dbc)
                dbc.Open()
                incmd.ExecuteNonQuery()
                dbc.Close()

                For x As Integer = 0 To Me.customersListdgv.Rows.Count - 1
                    Dim INCMD1 As New OleDbCommand("INSERT INTO tbStockOut(StockID,ProdName,Quantity,Price,OutDate,Description) VALUES(" & Me.customersListdgv.Rows(x).Cells(3).Value & ",'" & Me.customersListdgv.Rows(x).Cells(0).Value & "'," & Me.customersListdgv.Rows(x).Cells(1).Value & "," & Me.customersListdgv.Rows(x).Cells(2).Value & ",#" & Now() & "#,'SOLD')", dbc)
                    dbc.Open()
                    INCMD1.ExecuteNonQuery()
                    dbc.Close()
                Next


                Dim dlcmd As New OleDbCommand("DELETE FROM tbProductSold", dbc)
                dbc.Open()
                dlcmd.ExecuteNonQuery()
                dbc.Close()
                SoldProducts()





                If changee >= 0 Then
                    MessageBox.Show("Thank you Very Much", "Thank you", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    Me.change.Text = changee
                    showStocks()
                    SoldProducts()
                End If

            Catch ex As OleDbException

            End Try

            Me.Button2.Text = "SAVE"
            Me.Label9.Visible = False
            Me.amount.Visible = False
            Me.Button3.Visible = False
            Me.Button5.Visible = False

        End If





    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Button2.Text = "SAVE"
        Me.Label9.Visible = False
        Me.amount.Visible = False
        Me.Button3.Visible = False
        Me.amount.Text = ""



    End Sub

    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        Me.ToolStripStatusLabel1.Text = Now
    End Sub

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

            ' If Me.Button4.Text = "DELETE TRANSACTION" Then
            MessageBox.Show("DELETE")

            For x As Integer = 0 To Me.DataGridView2.RowCount - 1

                If Me.customersListdgv.CurrentRow.Cells(3).Value = Me.DataGridView2.Rows(x).Cells(0).Value Then

                    Dim upcmd As New OleDbCommand("UPDATE tbStocks SET Quantity=" & Me.customersListdgv.CurrentRow.Cells(2).Value + Me.DataGridView2.Rows(x).Cells(3).Value & " WHERE StockID=" & Me.DataGridView2.Rows(x).Cells(0).Value & ";", dbc)
                    dbc.Open()
                    upcmd.ExecuteNonQuery()
                    dbc.Close()

                    Dim dlcmd As New OleDbCommand("DELETE * FROM tbProductSold WHERE ProdID=" & Me.customersListdgv.CurrentRow.Cells(3).Value & ";", dbc)
                    dbc.Open()
                    dlcmd.ExecuteNonQuery()
                    dbc.Close()

                    SoldProducts()
                    showStocks()

                    If Me.customersListdgv.RowCount = 0 Then
                        Me.TextBox5.Text = ""
                        Me.tbill.Text = ""
                        Me.TextBox7.Text = ""
                        ' Me.Button4.Visible = False
                        Exit Sub

                    End If
                    If Me.customersListdgv.RowCount > 0 Then
                        totalBill()
                    End If

                    Exit Sub


                End If
            Next

            'ElseIf Me.Button4.Text = "UPDATE TRANSACTION" Then
            ' Dim diff As Integer = Me.DataGridView1.CurrentRow.Cells(3).Value - Me.NumericUpDown1.Value






            MessageBox.Show("The Order was Successfully Updated!")

            Dim Price As Integer = Me.customersListdgv.CurrentRow.Cells(1).Value / Me.customersListdgv.CurrentRow.Cells(2).Value

            For x As Integer = 0 To Me.DataGridView2.RowCount - 1

                If Me.customersListdgv.CurrentRow.Cells(3).Value = Me.DataGridView2.Rows(x).Cells(0).Value Then

                    'Dim upcmd As New OleDbCommand("UPDATE tbStocks SET Quantity=" & Me.DataGridView2.Rows(x).Cells(3).Value + Me.NumericUpDown1.Value & " WHERE StockID=" & Me.DataGridView2.Rows(x).Cells(0).Value & ";", dbc)
                    'dbc.Open()
                    'upcmd.ExecuteNonQuery()
                    'dbc.Close()

                    'Dim dlcmd As New OleDbCommand("UPDATE tbProductSold SET Quantity=" & Me.NumericUpDown1.Value & " WHERE ProdName='" & Me.DataGridView1.CurrentRow.Cells(0).Value & "';", dbc)
                    'dbc.Open()
                    'dlcmd.ExecuteNonQuery()
                    'dbc.Close()

                    SoldProducts()
                    Dim PPrice As Integer = Price * Me.customersListdgv.CurrentRow.Cells(2).Value

                    If Me.customersListdgv.CurrentRow.Cells(2).Value = 0 Then
                        Dim Delete As New OleDbCommand("DELETE * FROM tbProductSold WHERE ProdID='" & Me.customersListdgv.CurrentRow.Cells(3).Value & "';", dbc)
                        dbc.Open()
                        Delete.ExecuteNonQuery()
                        dbc.Close()
                    End If

                    SoldProducts()

                    Dim dlcmd1 As New OleDbCommand("UPDATE tbProductSold SET Price=" & PPrice & " WHERE ProdName='" & Me.customersListdgv.CurrentRow.Cells(0).Value & "';", dbc)
                    dbc.Open()
                    dlcmd1.ExecuteNonQuery()
                    dbc.Close()
                    SoldProducts()
                    showStocks()
                    If Me.customersListdgv.RowCount = 0 Then
                        Me.TextBox5.Text = ""
                        Me.tbill.Text = ""
                        Me.TextBox7.Text = ""
                        Me.Button5.Visible = False
                        Exit Sub

                    End If
                    If Me.customersListdgv.RowCount > 0 Then
                        totalBill()
                    End If


                    Exit Sub


                End If
            Next


            'End If

        Catch ex As OleDbException

        End Try

    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        For x As Integer = 0 To Me.DataGridView2.RowCount - 1
            Dim upcmd As New OleDbCommand("UPDATE tbStocks SET Quantity=" & Me.productsdgv.CurrentRow.Cells(3).Value + Me.customersListdgv.Rows(x).Cells(2).Value & " WHERE StockID=" & Me.productsdgv.Rows(x).Cells(0).Value & ";", dbc)
            dbc.Open()
            upcmd.ExecuteNonQuery()
            dbc.Close()

            Dim dlcmd As New OleDbCommand("DELETE * FROM tbProductSold WHERE ProdID=" & Me.productsdgv.CurrentRow.Cells(4).Value & ";", dbc)
            dbc.Open()
            dlcmd.ExecuteNonQuery()
            dbc.Close()

            SoldProducts()
            showStocks()

            If Me.productsdgv.RowCount = 0 Then
                Me.TextBox5.Text = ""
                Me.tbill.Text = ""
                Me.TextBox7.Text = ""
                Me.Button5.Visible = False
                Exit Sub

            End If
            If Me.productsdgv.RowCount > 0 Then
                totalBill()
            End If

            Exit Sub

        Next
    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(2).Value

    End Sub

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

    End Sub

    Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click
        Try


            Dim dba As New OleDbDataAdapter("SELECT * FROM tbSold WHERE ORNum=" & Me.TextBox1.Text & ";", dbc)
            Dim dbs As New DataSet
            dba.Fill(dbs)
            If dbs.Tables(0).DefaultView.Count = 0 Then
                AddClient.Show()

            ElseIf dbs.Tables(0).DefaultView.Count > 0 Then
                MessageBox.Show("The OR Number is already taken. Please insert another one.", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End If
        Catch ex As OleDbException
            MessageBox.Show("Invalid input.", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

    End Sub

    Private Sub Panel1_Paint(sender As Object, e As PaintEventArgs) Handles Panel1.Paint

    End Sub

    Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles customersListdgv.CellContentClick

    End Sub

    Private Sub TextBox8_TextChanged(sender As Object, e As EventArgs) Handles TextBox8.TextChanged
        SearchStocks()

    End Sub

    Private Sub Button4_Click_1(sender As Object, e As EventArgs) Handles Button4.Click
        If Me.TextBox1.Text = "" Or Me.TextBox2.Text = "" Then
            MessageBox.Show("Please Choose a Client", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End If
        If Me.NumericUpDown1.Value = 0 Then
            MessageBox.Show("The Quantity is 0 ", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End If
        If Me.NumericUpDown1.Value > Me.productsdgv.CurrentRow.Cells(3).Value Then
            MessageBox.Show("The Quantity you have Entered is beyond the Quantity Limit. ", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End If
        If Me.NumericUpDown1.Value > 0 Or Me.NumericUpDown1.Value <= Me.productsdgv.CurrentRow.Cells(3).Value Then

            For x As Integer = 0 To Me.productsdgv.Rows.Count - 1

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

                    Dim Update As New OleDbCommand("UPDATE tbStocks SET Quantity=" & Me.productsdgv.CurrentRow.Cells(3).Value - Me.NumericUpDown1.Value & " WHERE StockID=" & Me.productsdgv.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)
                    Me.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, Unit) VALUES(" & Me.productsdgv.CurrentRow.Cells(0).Value & ",'" & Me.productsdgv.CurrentRow.Cells(1).Value & "'," & Me.productsdgv.CurrentRow.Cells(2).Value * Me.NumericUpDown1.Value & "," & Me.NumericUpDown1.Value & ",'" & Me.productsdgv.CurrentRow.Cells(4).Value & "')", dbc)
            dbc.Open()
            incmd.ExecuteNonQuery()
            dbc.Close()
            'ShowProductSold()

            Dim upcmd As New OleDbCommand("UPDATE tbStocks SET Quantity=" & Me.productsdgv.CurrentRow.Cells(3).Value - Me.NumericUpDown1.Value & " WHERE StockID=" & Me.productsdgv.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)
            Me.tbill.Text = dbset.Tables(0).DefaultView.Item(0).Item(0)

            MessageBox.Show("The Product is Successfully Added to the Customer's Order List", "Added to Customer's List", 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
