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

    Private Sub FmOrder_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ConnectDatabase()
        txtOdeDate.Text = Today
        GetDocNo()
        UpdateDocNo()
        alert()
        txtOderID.Enabled = False
    End Sub

    Private Sub GetDocNo()
        gsql = "Select * From RUNDOC Where Doc_No = 'Order'"
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gsql
            .Connection = cnn
            dr = .ExecuteReader()
        End With
        dr.Read()
        If dr.HasRows Then
            txtOderID.Text = dr.Item("Run_No") + 1
        Else
            MessageBox.Show("ไม่พบข้อมูลเลขที่ใบสั่ง กรุณาตรวจสอบ")
        End If
        dr.Close()
    End Sub
    Private Sub UpdateDocNo()
        gsql = "Update RunDoc set Run_No='" & txtOderID.Text & "'" '
        gsql += " Where Doc_No = 'Order'"
        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 btnShowCompany_Click(sender As Object, e As EventArgs) Handles btnShowCompany.Click
        ShowCompuny.Show()
    End Sub

  
    Private Sub btnShowProducts_Click(sender As Object, e As EventArgs) Handles btnShowProducts.Click
        ShowProducts.Show()

    End Sub

    Private Sub btnAddP_Click(sender As Object, e As EventArgs) Handles btnAddP.Click
        If txtOderID.Text = "" Or txtP_Total.Text = "" Then
            MessageBox.Show("กรุณากรอกรายการสินค้า")

            Exit Sub
        End If

        listorder.Items.Add(txtP_ID.Text) '1=item
        listorder.Items(listorder.Items.Count - 1).SubItems.Add(txtP_Name.Text) '2'
        listorder.Items(listorder.Items.Count - 1).SubItems.Add(txtP_Unit.Text) '3'
        listorder.Items(listorder.Items.Count - 1).SubItems.Add(txtP_Total.Text) '4'
        listorder.Items(listorder.Items.Count - 1).SubItems.Add(Val(txtP_Unit.Text) * Val(txtP_Total.Text)) 'ราคา*จำนวน '5'


        MYcal()
        'เมื่อรวมราคา บันทึกเสร็จให้รายการทั้งหมดว่าง

        'txtP_ID.Text = ""
        'txtP_Name.Text = ""
        'txtP_Total.Text = ""
        'txtP_Unit.Text = 1
        'txtOderID.Focus()
    End Sub
    Private Sub MYcal() 'รวมราคา
        Dim total As Single
        total = 0
        If listorder.Items.Count > 0 Then
            For i = 0 To listorder.Items.Count - 1
                total += listorder.Items(i).SubItems(4).Text
            Next
            txtOderTotal.Text = Format(total, "#,#.00")
        End If
    End Sub

    
    Private Sub NewDoc()
        txtCPYID.Text = ""
        txtCPYName.Text = ""
        txtOderTotal.Text = "0.00"
        GetDocNo()
    End Sub
    Private Sub alert()
        Dim dt As New DataTable
        gsql = "select *from PRODUCTS where P_QTY<=20 "
        With cmd
            .CommandText = gsql
            .CommandType = CommandType.Text
            .Connection = cnn
            dr = .ExecuteReader
        End With
        dr.Read()
        If dr.HasRows Then
            dt.Load(dr)
            MessageBox.Show("สินค้าใกล้หมดแล้ว")
        Else
        End If
        dr.Close()

    End Sub

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

    Private Sub btnEdit_Click(sender As Object, e As EventArgs) Handles btnEdit.Click
        If listorder.SelectedItems.Count > 0 Then
            listorder.SelectedItems(0).SubItems(0).Text = txtP_ID.Text
            listorder.SelectedItems(0).SubItems(1).Text = txtP_Name.Text
            listorder.SelectedItems(0).SubItems(2).Text = txtP_Unit.Text
            listorder.SelectedItems(0).SubItems(3).Text = txtP_Total.Text
            listorder.SelectedItems(0).SubItems(4).Text = (txtP_Unit.Text * txtP_Total.Text)

            txtOderTotal.Text = listorder.SelectedItems(0).SubItems(4).Text

        End If
        MYcal()
    End Sub

    Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
        If listorder.SelectedItems.Count > 0 Then
            listorder.Items.Remove(listorder.SelectedItems(0))
        End If
        MYcal()
    End Sub

    Private Sub listorder_SelectedIndexChanged(sender As Object, e As EventArgs) Handles listorder.SelectedIndexChanged
        If listorder.SelectedItems.Count > 0 Then
            txtP_ID.Text = listorder.SelectedItems(0).SubItems(0).Text
            txtP_Name.Text = listorder.SelectedItems(0).SubItems(1).Text
            txtP_Unit.Text = listorder.SelectedItems(0).SubItems(2).Text()
            txtP_Total.Text = listorder.SelectedItems(0).SubItems(3).Text()
            txtOderTotal.Text = listorder.SelectedItems(0).SubItems(4).Text
        End If
        MYcal()
    End Sub

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

            gsql = "Insert into Oder_H values("
            gsql += "'" & txtOderID.Text & "',"
            gsql += "'" & txtCPYID.Text & "',"
            gsql += "'" & txtOderTotal.Text & "',"
            gsql += "'" & txtOdeDate.Text & "','ยังไม่ได้รับเข้า')"

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


            If listorder.Items.Count > 0 Then
                For i = 0 To listorder.Items.Count - 1
                    gsql = "Insert into Oder_Detail values("
                    gsql += "'" & txtOderID.Text & "',"
                    gsql += "'" & i + 1 & "',"
                    gsql += "'" & listorder.Items(i).Text & "',"
                    gsql += "'" & listorder.Items(i).SubItems(2).Text & "',"
                    gsql += "'" & listorder.Items(i).SubItems(3).Text & "',"
                    gsql += "'" & listorder.Items(i).SubItems(4).Text & "')"


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

                Next
                MessageBox.Show("บันทึกการสั่งซื้อแล้ว")


                UpdateDocNo()
                NewDoc()
                GetDocNo()
                cls_text()

            End If

        Catch ex As Exception
            MessageBox.Show("มีรหัสรับเข้านี้แล้วไม่สามารถเพิ่มได้หรือพบข้อผิดพลาดในฐานข้อมูล")
        End Try
    End Sub

    Private Sub btnClose_Click_1(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
        listorder.Items.Clear()
        gsql = "Select * From Oder_H where Oder_ID ='" & txtSearch.Text & "'"

        With cmd
            .CommandType = CommandType.Text
            .CommandText = gsql
            .Connection = cnn
            dr = .ExecuteReader
        End With

        dr.Read()
        If dr.HasRows Then
            txtOderTotal.Text = dr.Item("Oder_Total").ToString
            txtCPYID.Text = dr.Item("CPY_ID").ToString
        End If

        dr.Close()
        ShowCom()

        'ดึงข้อมูลจากตารางDetail

        gsql = "Select * From qryOrder_d where Oder_ID ='" & txtSearch.Text & "'"

        With cmd
            .CommandType = CommandType.Text
            .CommandText = gsql
            .Connection = cnn
            dr = .ExecuteReader
        End With

        While dr.Read

            listorder.Items.Add(dr.Item("P_ID"))
            listorder.Items(listorder.Items.Count - 1).SubItems.Add(dr.Item("P_Name"))
            listorder.Items(listorder.Items.Count - 1).SubItems.Add(dr.Item("P_Unit"))
            listorder.Items(listorder.Items.Count - 1).SubItems.Add(dr.Item("P_Sumprice"))
            listorder.Items(listorder.Items.Count - 1).SubItems.Add(dr.Item("P_SumTotal"))


        End While

        MYcal()
        dr.Close()

    End Sub
    Private Sub ShowCom()
        'คำสั่งSQL
        gsql = "Select * From COMPANY where CPY_ID = '" & txtCPYID.Text & "'" 'ใช้.ExecuteReader เท่านั้น 
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gsql
            .Connection = cnn
            dr = .ExecuteReader
        End With
        'อ่านข้อมูลมาแสดง
        dr.Read()
        If dr.HasRows Then
            txtCPYName.Text = dr.Item("CPY_Name").ToString

        End If
        dr.Close()
    End Sub

    Private Sub cls_text()
       listorder.Items.Clear()
        txtCPYID.Text = ""
        txtCPYName.Text = ""
        txtP_ID.Text = ""
        txtP_Name.Text = ""
        txtP_Total.Text = "0.00"
        txtP_Unit.Text = ""
        txtOderTotal.Text = "0.00"
    End Sub

End Class