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

    Private Sub FmProduct_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Label16.Text = Today
        ConnectDatabase()
        fillceoproducttype()
        fillceoproductUnit()

        ShowData()
        SetGrid()
        ShowList()


        TP_ID.Text = "เลือก"
        Unit_ID.Text = "เลือก"

    End Sub
    Private Sub ShowData()
        Dim dt As New DataTable
        gsql = "Select * from PRODUCTS"

        With cmd
            .CommandType = CommandType.Text
            .CommandText = gsql
            .Connection = cnn
            dr = .ExecuteReader
            If dr.HasRows Then
                dt.Load(dr)
                dgvProducts.DataSource = dt
            End If
            dr.Close()

        End With

    End Sub
    Private Sub ShowList()
        Dim dt As New DataTable
        gsql = "Select * from PRODUCTS"

        With cmd
            .CommandType = CommandType.Text
            .CommandText = gsql
            .Connection = cnn
            dr = .ExecuteReader
            If dr.HasRows Then
                dt.Load(dr)
                dgvProducts.DataSource = dt
            End If
            dr.Close()

        End With
        dt = Nothing


    End Sub

    Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
        Try


            gsql = "insert into PRODUCTS  Values ("
            gsql += "'" & P_ID.Text & "',"
            gsql += "'" & P_Name.Text & "',"
            gsql += "'" & TP_ID.Text & "',"
            gsql += "'" & Unit_ID.Text & "',"
            gsql += "'" & P_Cost.Text & "',"
            gsql += "'" & P_Sell.Text & "',"
            gsql += "'" & P_QTY.Text & "')"


            With cmd
                .CommandType = CommandType.Text
                .Connection = cnn
                .CommandText = gsql
                .ExecuteNonQuery()

            End With
            MessageBox.Show("เพิ่มข้อมุลใหม่แล้ว", "เพิ่มข้อมูล", MessageBoxButtons.OK, MessageBoxIcon.Information)

        Catch ex As Exception
            MessageBox.Show("มีรหัสสินค้านี้แล้วไม่สามารถเพิ่มได้", "เพิ่มข้อมูล", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Try
        ShowData()
        ClaerText()



    End Sub
    Private Sub ClaerText()
        P_ID.Text = ""
        P_Name.Text = ""
        TP_ID.Text = ""
        Unit_ID.Text = ""
        P_Cost.Text = ""
        P_Sell.Text = ""
        P_QTY.Text = ""
    End Sub

    Private Sub fillceoproducttype()
        gsql = "select*from TYPE"
        Dim da As New OleDbDataAdapter(gsql, cnn)
        Dim dt As New DataTable
        da.Fill(dt)
        With TP_ID
            .DataSource = dt
            .DisplayMember = dt.Columns.Item("TP_Name").ColumnName
            .ValueMember = dt.Columns.Item("TP_ID").ColumnName
        End With
    End Sub
    Private Sub fillceoproductUnit()
        gsql = "select*from Unit"
        Dim da As New OleDbDataAdapter(gsql, cnn)
        Dim dt As New DataTable
        da.Fill(dt)
        With Unit_ID
            .DataSource = dt
            .DisplayMember = dt.Columns.Item("Unit_Name").ColumnName
            .ValueMember = dt.Columns.Item("Unit_ID").ColumnName
        End With
    End Sub

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

   
    Private Sub SetGrid()
        If dgvProducts.RowCount > 0 Then
            dgvProducts.Columns(0).HeaderText = "รหัสสินค้า"
            dgvProducts.Columns(0).Width = 100
            dgvProducts.Columns(1).HeaderText = "รายการสินค้า"
            dgvProducts.Columns(1).Width = 300
            dgvProducts.Columns(2).HeaderText = "ประเภทสินค้า"
            dgvProducts.Columns(3).HeaderText = "หน่วยนับ"
            dgvProducts.Columns(4).HeaderText = "ราคาต้นทุน"
            dgvProducts.Columns(5).HeaderText = "ราคาขาย"
            dgvProducts.Columns(6).HeaderText = "จำนวนสินค้า"
        End If
    End Sub

    

    Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
        gsql = "Update PRODUCTS Set P_Name = '" & P_Name.Text & "',"
        gsql += "TP_ID = '" & TP_ID.Text & "',"
        gsql += "Unit_ID = '" & Unit_ID.Text & "',"
        gsql += "P_Cost= '" & P_Cost.Text & "',"
        gsql += "P_Sell= '" & P_Sell.Text & "',"
        gsql += " P_QTY= '" & P_QTY.Text & "'"
        gsql += " Where P_ID = '" & P_ID.Text & "'"
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gsql
            .Connection = Cnn
            .ExecuteNonQuery()
        End With
        MessageBox.Show("แก้ไขรายการใหม่", "แก้ไขข้อมูล", MessageBoxButtons.OK, MessageBoxIcon.Information)
        ShowData()
        ClaerText()


    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        Dim ans As MsgBoxResult
        ans = MessageBox.Show("ต้องการลบรายการหรือไม่", "ยืนยันการลบ", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
        If ans = MsgBoxResult.Yes Then
            gsql = "Delete From PRODUCTS Where P_ID = '" & P_ID.Text & "'"
            With cmd
                .CommandType = CommandType.Text
                .CommandText = gsql
                .Connection = cnn
                .ExecuteNonQuery()
            End With
            MessageBox.Show("ลบรายการแล้ว", "ลบข้อมูล", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If
        ShowData()
        ClaerText()
    End Sub

    Private Sub btnClose_Click(sender As Object, e As EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
        gsql = "Select * From PRODUCTS where P_ID ='" & txtSearch.Text & "'"
       
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gsql
            .Connection = cnn
            dr = .ExecuteReader
        End With
        dr.Read()
        If dr.HasRows Then
            P_ID.Text = dr.Item("P_ID").ToString
            P_Name.Text = dr.Item("P_Name").ToString
            TP_ID.Text = dr.Item("TP_ID").ToString
            Unit_ID.Text = dr.Item("Unit_ID").ToString
            P_Cost.Text = dr.Item("P_Cost").ToString
            P_Sell.Text = dr.Item("P_Sell").ToString
            P_QTY.Text = dr.Item("P_QTY").ToString

        Else
            MessageBox.Show("ไม่พบข้อมูล")

        End If
        dr.Close()


        btnSearch.Text = ""
    End Sub

    Private Sub dgvProducts_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles dgvProducts.CellContentClick
        Dim index As Integer

        index = e.RowIndex

        Dim selectedRow As DataGridViewRow

        selectedRow = dgvProducts.Rows(index)
        P_ID.Text = selectedRow.Cells(0).Value.ToString()
        P_Name.Text = selectedRow.Cells(1).Value.ToString()
        TP_ID.Text = selectedRow.Cells(2).Value.ToString()
        Unit_ID.Text = selectedRow.Cells(3).Value.ToString()
        P_Cost.Text = selectedRow.Cells(4).Value.ToString()
        P_Sell.Text = selectedRow.Cells(5).Value.ToString()
        P_QTY.Text = selectedRow.Cells(6).Value.ToString()




    End Sub

End Class