Imports System.Data
Imports System.Data.OleDb
Public Class FmSale
    Dim dr As OleDbDataReader
    Dim cmd As New OleDbCommand
    Dim gsql As String
    Private Sub FmSale_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ConnectDatabase()
        lblDate.Text = Today
        'txtSaleNo.Enabled = False

        GetDocNo()
        UpdateDocNo()
    End Sub

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

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

    Private Sub btn_ShowCus_Click(sender As Object, e As EventArgs) Handles btn_ShowCus.Click
        ShowCus.Show()
    End Sub

    Private Sub Button7_Click(sender As Object, e As EventArgs) Handles Button7.Click
        ShowProSale.Show()
    End Sub

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

            Exit Sub
        End If

        listorder.Items.Add(txtProductNo.Text) '1=item
        listorder.Items(listorder.Items.Count - 1).SubItems.Add(lbl_proName.Text) '2'
        listorder.Items(listorder.Items.Count - 1).SubItems.Add(numQTY.Value) '3'
        listorder.Items(listorder.Items.Count - 1).SubItems.Add(numQTY.Value * lbl_Price.Text)

        Cls()
        MYcal()
    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(3).Text
            Next
            txtOderTotal.Text = Format(total, "#,#.00")
        End If
    End Sub

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

            gsql = "Insert into Sale_H values("
            gsql += "'" & txt_saleNo.Text & "',"
            gsql += "'" & lblDate.Text & "',"
            gsql += "'" & txtCusNo.Text & "',"
            gsql += "'" & txtOderTotal.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 Sale_Detail values("
                    gsql += "'" & txt_saleNo.Text & "',"
                    gsql += "'" & i + 1 & "',"
                    gsql += "'" & listorder.Items(i).Text & "',"
                    gsql += "'" & listorder.Items(i).SubItems(2).Text & "',"
                    gsql += "'" & listorder.Items(i).SubItems(3).Text & "')"



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

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


                UpdateDocNo()
                cls_text()
                GetDocNo()


            End If

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


    Private Sub listorder_SelectedIndexChanged(sender As Object, e As EventArgs) Handles listorder.SelectedIndexChanged
        ShowPro()
        If listorder.SelectedItems.Count > 0 Then
            txtProductNo.Text = listorder.SelectedItems(0).SubItems(0).Text
            lbl_proName.Text = listorder.SelectedItems(0).SubItems(1).Text
            numQTY.Value = listorder.SelectedItems(0).SubItems(2).Text()
        End If

        MYcal()
    End Sub
    Private Sub ShowPro()
        gsql = "Select * From PRODUCTS Where P_ID ='" & txtProductNo.Text & "'"
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gsql
            .Connection = cnn
            dr = .ExecuteReader()
        End With
        dr.Read()
        If dr.HasRows Then
            lblTotal_H.Text = dr.Item("P_Cost").ToString
            lbl_Price.Text = dr.Item("P_Sell").ToString
        Else
            MessageBox.Show("ไม่พบข้อมูลเลขที่ใบสั่ง กรุณาตรวจสอบ")
        End If
        dr.Close()
    End Sub

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

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        If listorder.SelectedItems.Count > 0 Then
            listorder.SelectedItems(0).SubItems(0).Text = txtProductNo.Text
            listorder.SelectedItems(0).SubItems(1).Text = lbl_proName.Text
            listorder.SelectedItems(0).SubItems(2).Text = numQTY.Value
            listorder.SelectedItems(0).SubItems(3).Text = (numQTY.Value * lbl_Price.Text)

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

        End If
        MYcal()
    End Sub
    Private Sub Cls()
        txtProductNo.Text = ""
        lbl_Price.Text = ""
        lbl_proName.Text = ""
        lblTotal_H.Text = ""
        numQTY.Value = 1

    End Sub
    Private Sub cls_text()
        txtProductNo.Text = ""
        lbl_Price.Text = ""
        lbl_proName.Text = ""
        lblTotal_H.Text = ""
        numQTY.Value = 1
        txtCusNo.Text = ""
        lblCus_Name.Text = ""
        listorder.Items.Clear()
        txtOderTotal.Text = "0.00"
        txtCusNo.Focus()
    End Sub

End Class