Imports System.Data
Imports System.Data.OleDb

Public Class FmReceire
    Dim dr As OleDbDataReader
    Dim cmd As New OleDbCommand
    Dim gsql As String

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

    End Sub

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

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

        dr.Read()
        If dr.HasRows Then

            txtODate.Text = dr.Item("Oder_Date").ToString
            txtcomID.Text = dr.Item("CPY_ID").ToString
            txtcomName.Text = dr.Item("CPY_Name").ToString
            txtRTotal.Text = dr.Item("Oder_Total").ToString
            txtstatus.Text = dr.Item("Oder_Status").ToString
        End If

        dr.Close()
        ShowCom()

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

        gsql = "Select * From qryOrder_d where Oder_ID ='" & txtOderID.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_SumTotal"))

        End While

        MYcal()
        dr.Close()


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

        End If
        dr.Close()
    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 = total + listorder.Items(i).SubItems(3).Text
            Next
            txtRTotal.Text = Format(total, "#,#.00")
        End If
    End Sub

    Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
        listorder.Items.Clear()
        gsql = "Select * From Receive where ReCeive_ID ='" & txtSearch.Text & "'"

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

        dr.Read()
        If dr.HasRows Then
            txtRTotal.Text = dr.Item("ReCeive_ToTal").ToString
            txtReCeive_Date.Text = dr.Item("ReCeive_Date").ToString
            txtstatus.Text = dr.Item("Status").ToString
        End If

        dr.Close()



        gsql = "Select * From qryReceive_d where ReCeive_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("ReCeive_Unit"))
            listorder.Items(listorder.Items.Count - 1).SubItems.Add(dr.Item("ReCeive_ToTal"))

        End While

        MYcal()
        dr.Close()

    End Sub

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

            gsql = "Insert into ReCeive values("
            gsql += "'" & txtReCeive_ID.Text & "',"
            gsql += "'" & txtReCeive_Date.Text & "',"
            gsql += "'" & txtRTotal.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 Receive_Detail values("
                    gsql += "'" & txtReCeive_ID.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

                gsql = "Update Oder_H Set Oder_status = 'รับเข้าแล้ว' Where Oder_ID = '" & txtOderID.Text & "'"
                With cmd
                    .CommandType = CommandType.Text
                    .CommandText = gsql
                    .Connection = cnn
                    .ExecuteNonQuery()
                End With

                MessageBox.Show("บันทึกการรับเข้าแล้ว")
                AddStock()
                UpdateDocNo()
                Newdoc()
                GetDocNo()



            End If

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

    End Sub

    Private Sub AddStock()
        If listorder.Items.Count > 0 Then
            For i = 0 To listorder.Items.Count - 1 'วนทีละแถวใน Listview
                gsql = "Update PRODUCTS Set P_QTY = P_QTY + " & listorder.Items(i).SubItems(2).Text
                gsql += " Where P_ID = '" & listorder.Items(i).Text & "'"
                With cmd
                    .CommandType = CommandType.Text
                    .CommandText = gsql
                    .Connection = cnn
                    .ExecuteNonQuery()
                End With
            Next
        End If

    End Sub
    Private Sub Newdoc()
        listorder.Items.Clear()
        txtReCeive_ID.Text = ""
        txtReCeive_Date.Text = Today
        txtcomID.Text = ""
        txtcomName.Text = ""
        txtOderID.Text = ""
        txtODate.Text = Today
        txtstatus.Text = "รับเข้าแล้ว"
        txtRTotal.Text = "0.00"
        txtSearch.Text = ""
    End Sub

    Private Sub btnnew_Click(sender As Object, e As EventArgs)
        listorder.Items.Clear()
        txtReCeive_ID.Text = ""
        txtReCeive_Date.Text = Today
        txtcomID.Text = ""
        txtcomName.Text = ""
        txtRTotal.Text = "0.00"

    End Sub


End Class