Imports System.Data.SqlClient
Public Class SupCash
    Private da As SqlDataAdapter
    Private ds As DataSet
    Private dtSource As DataTable
    Private PageCount As Integer
    Private maxRec As Integer
    Private pageSize As Integer
    Private currentPage As Integer
    Private recNo As Integer

    'Dim conn As New OleDbConnection
    Dim sSql As String
    Dim ReceiptID As Integer
    Dim intSelectedRow As Integer
    Public Sub Userlog()
        Dim dd As String
        dd = Now.Date
        Try
            Call opencon()
            Dim myd As New SqlDataAdapter
            With myd
                .InsertCommand = New SqlCommand
                .InsertCommand.CommandText = "INSERT INTO loy(datte,uname,action)VALUES(@datte,@uname,@action) "
                .InsertCommand.Connection = con
                With .InsertCommand.Parameters
                    .AddWithValue("@datte", dd)
                    .AddWithValue("@uname", logger)
                    .AddWithValue("@action", action)
                End With
                .InsertCommand.ExecuteNonQuery()
            End With
            Call closecon()
        Catch ex As Exception
            MsgBox(ex.Message)
            Call closecon()
        End Try
    End Sub
    Private Sub SupCash_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        sSql = "SELECT category,price,qty from  stock"

        LoadDS(sSql)

        FillGrid()

    End Sub
    Private Function CheckFillButton() As Boolean
        'Check if the user clicks the "Fill Grid" button.
        If pageSize = 0 Then
            MessageBox.Show("Set the Page Size, and then click the ""Fill Grid"" button!")
            CheckFillButton = False
        Else
            CheckFillButton = True
        End If
    End Function
    Private Sub DisplayPageInfo()
        txtDisplayPageNo.Text = "Page " & currentPage.ToString & "/ " & PageCount.ToString
    End Sub
    Private Sub FillGrid()

        'Set the start and max records. 
        pageSize = 17 'txtPageSize.Text
        maxRec = dtSource.Rows.Count
        PageCount = maxRec \ pageSize

        ' Adjust the page number if the last page contains a partial page.
        If (maxRec Mod pageSize) > 0 Then
            PageCount = PageCount + 1
        End If

        'Initial seeings
        currentPage = 1
        recNo = 0

        ' Display the content of the current page.
        LoadPage()
    End Sub
    Private Sub LoadPage()
        Dim i As Integer
        Dim startRec As Integer
        Dim endRec As Integer
        Dim dtTemp As DataTable

        'Duplicate or clone the source table to create the temporary table.
        dtTemp = dtSource.Clone

        If currentPage = PageCount Then
            endRec = maxRec
        Else
            endRec = pageSize * currentPage
        End If

        startRec = recNo

        If dtSource.Rows.Count > 0 Then
            'Copy the rows from the source table to fill the temporary table.
            For i = startRec To endRec - 1
                dtTemp.ImportRow(dtSource.Rows(i))
                recNo = recNo + 1
            Next
        End If

        dgvList.DataSource = dtTemp

        DisplayPageInfo()
    End Sub
    Dim tb As New TextBox
    Dim ta As New TextBox
    Dim tt As Double = 0
    Private Sub LoadDS(ByVal sSQL As String)
        Try
            Dim cnString As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\InventData.mdf;Integrated Security=True;User Instance=True"
            'Open Connection.
            Dim conn As SqlConnection = New SqlConnection(cnString)

            'Set the DataAdapter's query.
            da = New SqlDataAdapter(sSQL, conn)
            ds = New DataSet()

            ' Fill the DataSet.
            da.Fill(ds, "Items")

            ' Set the source table.
            dtSource = ds.Tables("Items")
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error...", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

    End Sub

    Private Sub btnNextPage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNextPage.Click
        If Not CheckFillButton() Then Return

        'Check if the user clicked the "Fill Grid" button.
        If pageSize = 0 Then
            MessageBox.Show("Set the Page Size, and then click the ""Fill Grid"" button!")
            Return
        End If

        currentPage = currentPage + 1

        If currentPage > PageCount Then
            currentPage = PageCount

            'Check if you are already at the last page.
            If recNo = maxRec Then
                MessageBox.Show("You are at the Last Page!")
                Return
            End If
        End If

        LoadPage()
    End Sub

    Private Sub btnLastPage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLastPage.Click
        If Not CheckFillButton() Then Return

        ' Check if you are already at the last page.
        If recNo = maxRec Then
            MessageBox.Show("You are at the Last Page!")
            Return
        End If

        currentPage = PageCount

        recNo = pageSize * (currentPage - 1)

        LoadPage()
    End Sub

    Private Sub btnFirstPage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirstPage.Click
        If Not CheckFillButton() Then Return

        ' Check if you are already at the first page.
        If currentPage = 1 Then
            MessageBox.Show("You are at the First Page!")
            Return
        End If

        currentPage = 1
        recNo = 0

        LoadPage()
    End Sub

    Private Sub btnPreviousPage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPreviousPage.Click
        If Not CheckFillButton() Then Return

        'If currentPage = PageCount Then
        '    recNo = pageSize * (currentPage - 2)
        'End If

        currentPage = currentPage - 1

        'Check if you are already at the first page.
        If currentPage < 1 Then
            MessageBox.Show("You are at the First Page!")
            currentPage = 1
            Return
        Else
            recNo = pageSize * (currentPage - 1)
        End If

        LoadPage()
    End Sub

    Private Sub dgvList_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvList.CellEndEdit
        Dim Inv As DataGridView = DirectCast(sender, DataGridView)

        If IsDBNull(Inv("Qpty", e.RowIndex).Value) Or IsDBNull(Inv("Price", e.RowIndex).Value) Then Exit Sub
       
        Dim gt As Double = Inv("Qpty", e.RowIndex).Value
        Dim gtr As Double = Inv("qty", e.RowIndex).Value
        Inv("qty", e.RowIndex).Value = gt + gtr
        Try
            Call opencon()
            Dim myd2 As New SqlDataAdapter
            With myd2
                .UpdateCommand = New SqlCommand
                .UpdateCommand.CommandText = "UPDATE stock SET [qty] = @qty WHERE category LIKE '%%" & Inv("Category", e.RowIndex).Value & "%%'"
                .UpdateCommand.Connection = con
                With .UpdateCommand.Parameters
                    .AddWithValue("@qty", Inv("qty", e.RowIndex).Value)
                End With
                .UpdateCommand.ExecuteNonQuery()
            End With

            Call closecon()

        Catch ex As Exception
            MsgBox(ex.Message)
            Call closecon()
        End Try


        Dim curQty As Double = Inv("Qpty", e.RowIndex).Value
        Dim curSellingPrice As Double = Inv("Price", e.RowIndex).Value

        Inv("qqty", e.RowIndex).Value = FormatNumber(curQty * curSellingPrice)
        Me.txttotal.Text = Val(Me.txttotal.Text) + (curQty * curSellingPrice)


        'Dim goods As String = Inv("Category", e.RowIndex).Value
        'tb.Text = tb.Text + goods + ";"
        'Dim quant As String = Inv("qqty", e.RowIndex).Value
        'ta.Text = ta.Text + quant + ";"
    End Sub

   

    Private Sub btn_close_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_close.Click
        Me.Close()
        ' insert()
    End Sub

    Private Sub btn_Refresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Refresh.Click
        txtname.Clear()
    End Sub
    Public Sub insert()
        Try
            Call opencon()
            Dim myda As New SqlDataAdapter
            With myda
                .SelectCommand = New SqlCommand
                .SelectCommand.CommandText = "SELECT supname,category,address,pnumber INTO supp FROM supplier " 'WHERE supname LIKE '%%" & Me.txtname.Text & "%%'"
                .SelectCommand.Connection = con
                .SelectCommand.ExecuteNonQuery()
            End With
            Call closecon()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    Private Sub btn_Add_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Add.Click
        If Me.txtname.Text.Length = 0 Then
            MessageBox.Show("Please enter suppliers name", "WARNING", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        Else
            Try
                Call opencon()
                Dim myda As New SqlDataAdapter
                With myda
                    .SelectCommand = New SqlCommand
                    .SelectCommand.CommandText = "SELECT * FROM supplier WHERE supname LIKE '%%" & txtname.Text & "%%'"
                    .SelectCommand.Connection = con
                End With
                Dim dr As New DataSet
                myda.Fill(dr, "myTable")
                If dr.Tables("myTable").Rows.Count > 0 Then
                    MsgBox("Supplier Name already exist...", MsgBoxStyle.Exclamation, "Alert...")
                    DialogResult = MessageBox.Show("Do you want to update his/her record", "ALERT", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)
                    If DialogResult = Windows.Forms.DialogResult.Yes Then
                        Me.Hide()
                        frmSupp.ShowDialog()
                    Else
                        Exit Sub
                    End If

                Else
                    Me.Hide()
                    frmSupp.ShowDialog()
                End If
                Call closecon()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End If
    End Sub

    Private Sub txtname_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtname.Leave
        Me.txtname.Text = StrConv(Me.txtname.Text, vbProperCase)
    End Sub

    Private Sub txtname_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtname.TextChanged

    End Sub
End Class