Imports System.Data
Imports System.Data.OleDb
Public Class FmRevenue
    Dim dr As OleDbDataReader
    Dim cmd As New OleDbCommand
    Dim gSQL As String
    Private Sub FmRevenue_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ConnectDatabase()
        Label16.Text = Today
        GetDocNo()
        showlist()
        SetGrid()
    End Sub
    Private Sub showlist() 'dataGridView
        Dim dt As New DataTable
        gSQL = "select * from Revenue"
        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 GetDocNo()
        gSQL = "Select * From RUNDOC Where Doc_No = 'Re'"
        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 = 'Re'"
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gsql
            .Connection = cnn
            .ExecuteNonQuery()

        End With

    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 Revenue Values ("
        gSQL += "'" & TextBox3.Text & "',"
        gSQL += "'" & TextBox4.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)
       
        showlist()
        UpdateDocNo()
        GetDocNo()
        TextBox1.Text = 0
        TextBox4.Text = ""

    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        gSQL = "Update Revenue Set Revenue_Name = '" & TextBox4.Text & "',"
        gSQL += "Revenue_price = '" & TextBox1.Text & "'"

        'จะไปอัปเดตค่าที่ตรงไหน gSQL += " Where ProductNo
        gSQL += " Where Revenue_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
        TextBox4.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 Revenue Where Revenue_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
        TextBox4.Text = ""
    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 = 170
            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
        TextBox4.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 Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Me.Close()
    End Sub
End Class