Imports System.Data
Imports System.Data.OleDb
Public Class FmExpense
    Dim dr As OleDbDataReader
    Dim cmd As New OleDbCommand
    Dim gsql As String

    Private Sub FmExpense_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ConnectDatabase()
        Label16.Text = Today
        GetDocNo()

        showlist()
        SetGrid()
        Smm()
    End Sub

    Private Sub GetDocNo()
        gsql = "Select * From RUNDOC Where Doc_No = 'Ex'"
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gsql
            .Connection = cnn
            dr = .ExecuteReader()
        End With
        dr.Read()
        If dr.HasRows Then
            TextBox3.Text = dr.Item("Run_No") + 1
        Else
            MessageBox.Show("ไม่พบข้อมูลเลขที่ใบสั่ง กรุณาตรวจสอบ")
        End If
        dr.Close()
    End Sub
    Private Sub UpdateDocNo()
        gsql = "Update RunDoc set Run_No='" & TextBox3.Text & "'" '
        gsql += " Where Doc_No = 'Ex'"
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gsql
            .Connection = cnn
            .ExecuteNonQuery()

        End With

    End Sub
    Private Sub showlist() 'dataGridView
        Dim dt As New DataTable
        gsql = "select * from Expense"
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gsql
            .Connection = cnn
            dr = .ExecuteReader
        End With
        If dr.HasRows Then
            dt.Load(dr)
            DataGridView1.DataSource = dt 'dtโหลด dr ให้ dgv=dt
        End If
        dr.Close()
    End Sub

    Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
        Main.Show()
        Me.Hide()
    End Sub

    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click

        gsql = "Insert Into Expense Values ("
        gsql += "'" & TextBox3.Text & "',"
        gsql += "'" & TextBox2.Text & "',"
        gsql += "'" & TextBox1.Text & "',"
        gsql += "'" & DateTimePicker2.Text & "')"


        With cmd
            .CommandType = CommandType.Text
            .CommandText = gsql
            .Connection = cnn
            .ExecuteNonQuery()
        End With
        MessageBox.Show("เพิ่มรายจ่ายแล้ว", "เพิ่มข้อมูล", MessageBoxButtons.OK, MessageBoxIcon.Information)

        UpdateDocNo()
        GetDocNo()
        showlist()
        TextBox1.Text = 0
        TextBox2.Text = ""
        Smm()
    End Sub
    Private Sub SetGrid() 'สร้างข้อความหัวคอลัมน์ แก้ไขหัวคอลัมน์ตามต้องการ
        If DataGridView1.RowCount > 0 Then
            DataGridView1.Columns(0).HeaderText = "รหัสรายจ่าย"
            DataGridView1.Columns(0).Width = 150
            DataGridView1.Columns(1).HeaderText = "รายการ"
            DataGridView1.Columns(1).Width = 300
            DataGridView1.Columns(2).HeaderText = "จำนวนเงิน"
            DataGridView1.Columns(2).Width = 200
            DataGridView1.Columns(3).HeaderText = "วันที่"
            DataGridView1.Columns(3).Width = 200



        End If
    End Sub

    Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
        TextBox3.Text = DataGridView1.Rows(e.RowIndex).Cells(0).Value.ToString
        TextBox2.Text = DataGridView1.Rows(e.RowIndex).Cells(1).Value.ToString
        TextBox1.Text = DataGridView1.Rows(e.RowIndex).Cells(2).Value.ToString
        DateTimePicker2.Text = DataGridView1.Rows(e.RowIndex).Cells(3).Value.ToString
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        gsql = "Update Expense Set Expense_Name = '" & TextBox2.Text & "',"
        gsql += "Expense_price = '" & TextBox1.Text & "'"

        'จะไปอัปเดตค่าที่ตรงไหน gSQL += " Where ProductNo
        gsql += " Where Expense_id = '" & TextBox3.Text & "'"

        'cmd ให้ cmd รันคำสั่ง
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gsql
            .Connection = cnn
            .ExecuteNonQuery()
        End With
        MessageBox.Show("แก้ไขรายการใหม่", "แก้ไขข้อมูล", MessageBoxButtons.OK, MessageBoxIcon.Information)

        showlist()
        TextBox1.Text = 0
        TextBox2.Text = ""
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim ans As MsgBoxResult
        ans = MessageBox.Show("ต้องการลบรายการหรือไม่", "ยืนยันการลบ", MessageBoxButtons.YesNo,
MessageBoxIcon.Question)
        If ans = MsgBoxResult.Yes Then
            gsql = "Delete From Expense Where Expense_id = '" & TextBox3.Text & "'"
            With cmd
                .CommandType = CommandType.Text
                .CommandText = gsql
                .Connection = cnn
                .ExecuteNonQuery()
            End With
        End If
        MessageBox.Show("ลบรายการแล้ว", "ลบข้อมูล", MessageBoxButtons.OK, MessageBoxIcon.Information)
        showlist()
        TextBox1.Text = 0
        TextBox2.Text = ""
    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Me.Close()
    End Sub
    Private Sub Smm()
        Dim i As Integer = 0
        Dim Total As Double = 0
        For i = 0 To DataGridView1.Rows.Count - 1
            Total += CDbl(DataGridView1.Rows(i).Cells(2).Value)

        Next
        TextBox4.Text = Total.ToString("#,##0.00")

    End Sub

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

    End Sub


End Class