Imports System.Data.OleDb
Imports System.Object
Public Class invoices

    Private Sub invoices_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call connectDatabase()
        Call getLastID()

        Call getfastID()
        Call getTotals()
        Call cleartextbox()

        DateAddedLabel1.Text = Format(Date.Today, "Short Date").ToString
        Label11.Text = Form1.ComboBox1.Text()
        
    End Sub
    Private Sub cleartextbox()
        TextBox1.Text = ""
        ItemNameTextBox.Text = ""
        QtyTextBox.Text = ""
        PriceTextBox.Text = ""
        AmountTextBox.Text = ""
        TextBox2.Text = ""
        TextBox3.Text = ""
        TextBox4.Text = ""

    End Sub

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

    Private Sub AmountTextBox_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AmountTextBox.TextChanged
        If IsValidTextbox(AmountTextBox) = False Then Exit Sub

    End Sub

    Private Sub QtyTextBox_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles QtyTextBox.TextChanged
        If IsValidTextboxForNumbers(QtyTextBox) = False Then Exit Sub
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            AmountTextBox.Text = PriceTextBox.Text * QtyTextBox.Text
        Catch
        End Try
        If QtyTextBox.Text = "" Then
            MsgBox("Input Qty")
        ElseIf ItemNameTextBox.Text = "" Then
            MsgBox("Input Products")
        Else
            'continue program
            Call sss()

            Call cleartextbox()
            Call loadOrder()
        End If
        Me.TextBox2.Text = Totals()
      
    End Sub

    Private Sub getLastID()
        Dim oleDBDR As OleDbDataReader
        Dim oleDBCommand As New OleDbCommand

        With oleDBCommand
            .Connection = conn
            .CommandText = "SELECT * FROM tblInvoicess ORDER BY InvoiceNo DESC"
        End With
        oleDBDR = oleDBCommand.ExecuteReader
        If oleDBDR.Read Then
            InvoiceNo1.Text = Val(oleDBDR.Item(0)) + 1
        End If
    End Sub

    Private Sub getfastID()
        Dim oleDBDR As OleDbDataReader
        Dim oleDBCommand As New OleDbCommand

        With oleDBCommand
            .Connection = conn
            .CommandText = "SELECT * FROM tblAddOrder ORDER BY OrNo DESC"
        End With
        oleDBDR = oleDBCommand.ExecuteReader
        If oleDBDR.Read Then
            OrNo1.Text = Val(oleDBDR.Item(0)) + 1
        End If
    End Sub


    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        TextBox1.Text = ""
        ItemNameTextBox.Text = ""

        QtyTextBox.Text = ""
        PriceTextBox.Text = ""
        DateAddedLabel1.Text = ""
        AmountTextBox.Text = ""

        Call DisconnectDatabase()
        Me.Hide()
    End Sub
    Private Sub loadsearchresult()
        Dim OleDBC As New OleDbCommand
        Dim OleDBDR As OleDbDataReader
        Dim c As Integer
        c = 0
        With OleDBC

            .Connection = conn
            .CommandText = "SELECT * FROM tblAddOrder"

        End With
        OleDBDR = OleDBC.ExecuteReader
        DataGridView1.Rows.Clear()
        If OleDBDR.HasRows Then
            While OleDBDR.Read
                DataGridView1.Rows.Add()
                DataGridView1.Item(1, c).Value = OleDBDR.Item(2)
                DataGridView1.Item(2, c).Value = OleDBDR.Item(5)
                DataGridView1.Item(3, c).Value = OleDBDR.Item(6)
                DataGridView1.Item(4, c).Value = OleDBDR.Item(7)

                c = c + 1
            End While
        End If
    End Sub
    Private Sub getTotals()
        Try
            Dim I As Integer
            For I = 0 To DataGridView1.RowCount - 1
                TextBox2.Text = Val(Format(TextBox2.Text, "General Number")) + Val(DataGridView1.Rows.Item(I).Cells(4).Value)
                TextBox2.Text = Format(TextBox2.Text, "Standard")
                TextBox3.Text = Format(TextBox3.Text, "Standard")
            Next
        Catch
        End Try
    End Sub


    Private Sub PriceTextBox_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PriceTextBox.TextChanged
        If IsValidTextbox(PriceTextBox) = False Then Exit Sub
    End Sub

    Private Sub sss()
        Try
            Dim oleDC As New OleDbCommand
            Dim qh As Integer
            Dim tq As Integer
            Dim qs As Integer
            qh = CInt(Label1.Text)
            tq = CInt(Label4.Text)
            qs = CInt(Label5.Text)
            qh = qh - tq
            qs = tq + qs
            AmountTextBox.Text = QtyTextBox.Text * CInt(PriceTextBox.Text)
            Label1.Text = qh
            Label5.Text = qs

            With oleDC
                .Connection = conn
                .CommandText = "UPDATE tblStock SET QtyonHand='" & Label1.Text & _
                    "',Qty='" & Label5.Text & _
                    "' WHERE Barcode='" & Barcode1.Text & "'"
                .ExecuteNonQuery()
                If Label1.Text = "0" Then
                    MsgBox("Out of Stock")
                    CheckBox1.Checked = False
                Else
                    CheckBox1.Checked = True
                End If
            End With
        Catch
        End Try
        If MsgBox("Are you sure you want to Add Data?", MsgBoxStyle.Question + MsgBoxStyle.YesNo, "WARNING") = MsgBoxResult.Yes Then
            Dim OleDBC As New OleDbCommand
            With OleDBC
                .Connection = conn
                .CommandText = "Insert Into tblAddOrder VALUES ('" & OrNo1.Text & _
                "','" & DateAddedLabel1.Text & _
                "','" & Barcode1.Text & _
                "','" & TextBox1.Text & _
                "','" & ItemNameTextBox.Text & _
                "','" & QtyTextBox.Text & _
                "','" & PriceTextBox.Text & _
                "','" & AmountTextBox.Text & "')"
                .ExecuteNonQuery()

            End With
            MsgBox("Data Added!", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "SUCCESS")
        End If
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        Call DisconnectDatabase()
        frmstocktorecord.ShowDialog()

    End Sub

    Private Sub getCompute()
        Try
            Dim oleDBDR As OleDbDataReader
            Dim oleDBCommand As New OleDbCommand

            With oleDBCommand
                .Connection = conn
                .CommandText = "SELECT * FROM tblStock"
            End With
            oleDBDR = oleDBCommand.ExecuteReader
            If oleDBDR.Read Then
                AmountTextBox.Text = Val(oleDBDR.Item(9)) * QtyTextBox.Text
            End If
        Catch
        End Try

    End Sub

    Private Sub DataGridView1_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit

        Dim Inv As DataGridView = DirectCast(sender, DataGridView)

        If IsDBNull(Inv("Qty", e.RowIndex).Value) Or IsDBNull(Inv("OrderNo", e.RowIndex).Value) Then Exit Sub

        Dim intProductID As Integer = Inv("Barcode", e.RowIndex).Value

        Dim sqlQRY As String = "SELECT * FROM tblAddOrder WHERE OrderNo = " & intProductID

        Dim cmd As OleDbCommand = New OleDbCommand(sqlQRY, conn)


        'create data reader
        Dim rdr As OleDbDataReader = cmd.ExecuteReader

        rdr.Read()

        Dim curPrice As Decimal = rdr("Price").ToString

        Inv("Price", e.RowIndex).Value = curPrice

        Inv("Amount", e.RowIndex).Value = Inv("Qty", e.RowIndex).Value * Inv("Price", e.RowIndex).Value

        Me.TextBox2.Text = Totals()
    End Sub

    Private Function Totals() As Double
        Dim total As Double = 0
        Dim i As Integer = 0

        For i = 0 To DataGridView1.Rows.Count - 1
            total = total + Convert.ToDouble(DataGridView1.Rows(i).Cells("Amount").Value)
        Next i

        Return total
    End Function


    Private Sub DataGridView1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGridView1.Click
        Me.TextBox2.Text = Totals()
    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        Try
            TextBox4.Text = TextBox3.Text - TextBox2.Text

        Catch
        End Try
        frmAELreciept.Label2.Text = OrNo1.Text
        frmAELreciept.Label4.Text = DateAddedLabel1.Text
        frmAELreciept.Label6.Text = TextBox2.Text
        frmAELreciept.Label8.Text = TextBox3.Text
        frmAELreciept.Label10.Text = TextBox4.Text
        frmAELreciept.Label12.Text = Label11.Text
        frmAELreciept.Label13.Text = InvoiceNo1.Text
        Me.Hide()
        Call DisconnectDatabase()
        frmAELreciept.Show()
       

    End Sub
    Private Sub loadOrder()
        Dim oleDBC As New OleDbCommand
        Dim oleDBDR As OleDbDataReader
        Dim c As Integer
        c = 0
        With oleDBC
            .Connection = conn
            .CommandText = "SELECT * FROM tblAddOrder Where OrNo='" & OrNo1.Text & "'"
        End With
        oleDBDR = oleDBC.ExecuteReader
        DataGridView1.Rows.Clear()
        If oleDBDR.HasRows Then
            While oleDBDR.Read
                DataGridView1.Rows.Add()
                DataGridView1.Item(0, c).Value = oleDBDR.Item(2)
                DataGridView1.Item(1, c).Value = oleDBDR.Item(5)
                DataGridView1.Item(2, c).Value = oleDBDR.Item(6)
                DataGridView1.Item(3, c).Value = oleDBDR.Item(7)
                'DataGridView1.Item(4, c).Value = oleDBDR.Item(6)
                c = c + 1
            End While
        Else
        End If
    End Sub

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

    Private Sub TextBox4_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox4.TextChanged
        Try
           
            TextBox4.Text = CSng(TextBox3.Text) - CSng(TextBox2.Text)
        Catch
        End Try
    End Sub

    Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
        Call connectDatabase()

        Call getLastID()

        Call getfastID()
        Call getTotals()
        Call cleartextbox()

        DateAddedLabel1.Text = Format(Date.Today, "Short Date").ToString
        Label11.Text = Form1.ComboBox1.Text()
    End Sub

    Private Sub TextBox3_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox3.TextChanged
        Try
            TextBox4.Text = TextBox3.Text - TextBox2.Text
        Catch
        End Try
    End Sub

    Private Sub InvoiceNo1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles InvoiceNo1.Click

    End Sub

    Private Sub Label11_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label11.Click

    End Sub

    Private Sub Label11_GotFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles Label11.GotFocus
        Label11.Text = Form1.ComboBox1.Text
    End Sub

    Private Sub Label11_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Label11.TextChanged
        Label11.Text = Form1.ComboBox1.Text
    End Sub
End Class