Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Public Class wapprovs
Dim objCon As New dbcon
Dim conn As New SqlConnection(dbcon.conString)
Private Sub wapprovs_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
ShowPasadusData()
Format_Grid3()
lblwAppId.Text = getNextId()
Dim time As Date
time = Date.Now.ToShortDateString()
GroupBox1.Enabled = True
cmdSave.Enabled = False
cmdEdit.Enabled = False
cmdDelete.Enabled = False
End Sub
Function getNextId() As String
'สั่งให้รันรหัสเองไปเรื่อยๆ
conn.Open()
Dim sqlStr As String
sqlStr = "Select TOP(1) WApproves_id From tbWApprovs order by WApproves_id DESC "
Dim cmd As New SqlCommand(sqlStr, conn)
Dim dr As SqlDataReader
dr = cmd.ExecuteReader
Dim id As Integer = 0
If dr.HasRows = True Then
dr.Read()
'ตัดตัว Wa ออก เริ่มเอามาจากตัวที่ 2
id = dr("WApproves_id").ToString.Substring(2, 4)
dr.Close()
End If
conn.Close()
id = id + 1
Return "Wa" & id.ToString("0000")
End Function
Private Sub ShowPasadusData()
Dim sql3 As String = String.Empty
conn.Open()
sql3 = "select Pasadu_id,Pasadu_name,Price,Qty_pas,Qty,Unit,Shop from tbPasadus"
Dim ds = New DataSet
Dim da = New SqlClient.SqlDataAdapter(sql3, conn)
da.Fill(ds, "tbPasadus")
If ds.tables("tbPasadus").rows.count <> 0 Then
dgspas.DataSource = ds.tables("tbPasadus")
End If
conn.Close()
Call Format_Grid()
End Sub
Private Sub Format_Grid()
With dgspas
' If .RowCount > 0 Then
.Columns(0).HeaderText = "รหัสพัสดุ"
.Columns(1).HeaderText = "ชื่อพัสดุ"
.Columns(2).HeaderText = "ราคาทุนต่อหน่วย"
.Columns(3).HeaderText = "จานวนพัสดุ"
.Columns(4).HeaderText = "จานวนคงเหลือ"
.Columns(5).HeaderText = "หน่วยนับ"
.Columns(6).HeaderText = "ร้านค้า"
.Columns(0).DataPropertyName = "Pasadu_id"
.Columns(1).DataPropertyName = "Pasadu_name"
.Columns(2).DataPropertyName = "Price"
.Columns(3).DataPropertyName = "Qty_pas"
.Columns(4).DataPropertyName = "Qty"
.Columns(5).DataPropertyName = "Unit"
.Columns(6).DataPropertyName = "Shop"
.Columns(0).Width = 110
.Columns(1).Width = 120
.Columns(2).Width = 120
.Columns(3).Width = 100
.Columns(4).Width = 110
.Columns(5).Width = 90
.Columns(6).Width = 160
' End If
End With
End Sub
Private Sub ClearData() 'เคลียร์ค่าข้อมูลให้ว่าง
txtID.Text = ""
txtName.Text = ""
txtPrice.Text = ""
txtQty.Text = ""
txtPerso.Text = ""
txtTotal.Text = ""
lblNetOders.Text = ""
dgvProductApp.DataSource = Nothing 'เพิ่มมาอีก
End Sub
Private Sub ShowdtWpp() 'นาไปโชว์
Dim sql3 As String = String.Empty
conn.Open()
sql3 = "Select WApproves_id,Pasadu_id,Pasadu_name,CostPrice,AppQty,AppTotal,Status From tbWApprovs_detail where WApproves_id = '" & Trim(lblwAppId.Text) & "'"
Dim ds = New DataSet
Dim da = New SqlClient.SqlDataAdapter(sql3, conn)
da.Fill(ds, "tbWApprovs_detail")
'0If ds.tables("tbWApprovs_detail").rows.count <> 0 Then
dgvProductApp.DataSource = ds.tables("tbWApprovs_detail")
Dim sum As Single
Dim i As Integer = 0
For i = 0 To ds.tables("tbWApprovs_detail").rows.count - 1
sum += ds.tables("tbWApprovs_detail").rows(i)("AppTotal")
Next
lblNetOders.Text = sum '.ToString("#,##0.00")
' lblNetOders.Text = sum
txtTotal.Text = ds.tables("tbWApprovs_detail").rows.count
'End If
conn.Close()
Call Format_Grid3()
End Sub
Private Sub Format_Grid3()
With dgvProductApp
If .RowCount > 0 Then
.Columns(0).HeaderText = "รหัสการรอขออนุมัติ"
.Columns(1).HeaderText = "รหัสพัสดุ"
.Columns(2).HeaderText = "ชื่อพัสดุ"
.Columns(3).HeaderText = "ราคาต่อหน่วย"
.Columns(4).HeaderText = "จานวนที่สั่ง"
.Columns(5).HeaderText = "ราคารวม"
.Columns(6).HeaderText = "สถานะ"
.Columns(0).DataPropertyName = "WApproves_id"
.Columns(1).DataPropertyName = "Pasadu_id"
.Columns(2).DataPropertyName = "Pasadu_name"
.Columns(3).DataPropertyName = "CostPrice"
.Columns(4).DataPropertyName = "AppQty"
.Columns(5).DataPropertyName = "AppTotal"
.Columns(6).DataPropertyName = "Status"
.Columns(0).Width = 160
.Columns(1).Width = 110
.Columns(2).Width = 120
.Columns(3).Width = 90
.Columns(4).Width = 90
.Columns(5).Width = 90
.Columns(6).Width = 50
End If
End With
End Sub
Private Sub ClearProOrders()
txtID.Text = String.Empty
txtName.Text = String.Empty
txtPrice.Text = String.Empty
txtQty.Text = String.Empty
lblSaleTotal.Text = String.Empty
End Sub
Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
'Call UpdateWApprovs_detail() 'เพิ่มมาอีก
Call InsertWApp()
Call ClearData()
cmdSave.Enabled = True
Call getNextId()
lblwAppId.Text = getNextId()
dgvProductApp.DataSource = Nothing
End Sub
Private Sub dgspas_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgspas.CellContentClick
If e.RowIndex = -1 Then Exit Sub
With dgspas
txtID.Text = .Rows.Item(e.RowIndex).Cells(0).Value.ToString
txtName.Text = .Rows.Item(e.RowIndex).Cells(1).Value.ToString
txtPrice.Text = .Rows.Item(e.RowIndex).Cells(2).Value.ToString
End With
GroupBox1.Enabled = True
txtQty.Focus()
End Sub
Private Sub InsertWApp()
Dim coonet As SqlConnection = New SqlConnection
Dim comsave As New SqlClient.SqlCommand
Dim sqllog As String = "Data Source=(local);Initial Catalog=wpp;User ID=sa;Password=050301"
coonet.ConnectionString = sqllog
sqllog = "INSERT INTO tbWApprovs(WApproves_id,Net,WApprovesDate)" & _
" VALUES (@WApproves_id,@Net,@WApprovesDate)"
comsave = New SqlCommand(sqllog, coonet)
With comsave
.Parameters.AddWithValue("@WApproves_id", (lblwAppId.Text))
.Parameters.AddWithValue("@Net", Trim(lblNetOders.Text))
.Parameters.Add("@WApprovesDate", SqlDbType.DateTime).Value = dtDate.Value.ToShortDateString
End With
If (coonet.State = ConnectionState.Closed) Then coonet.Open()
comsave.ExecuteNonQuery()
coonet.Close()
MessageBox.Show("บันทึกการรออนุมัติเรียบร้อยแล้วค่ะ", "ผลการดาเนินงาน", MessageBoxButtons.OK)
End Sub
Private Sub InsertPro()
Dim sql As String = String.Empty
Dim comsave As New SqlClient.SqlCommand
conn.Open()
sql = "insert into tbWApprovs_detail"
sql &= "(WApproves_id,Pasadu_id,Pasadu_name,CostPrice,AppQty,AppTotal,Status)"
sql &= "values ('" & Trim(lblwAppId.Text) & "','" & Trim(txtID.Text) & "','" & RTrim(txtName.Text) & "','" & RTrim(txtPrice.Text) & "', '" & RTrim(txtQty.Text) & "','" & RTrim(lblSaleTotal.Text) & "','" & RTrim(txtStatus.Text) & "')"
With comsave
.CommandText = sql
.CommandType = CommandType.Text
.Connection = conn
.Parameters.Clear()
.ExecuteNonQuery()
End With
conn.Close()
MessageBox.Show("เพิ่มการรออนุมัติเรียบร้อยแล้วค่ะ", "ผลการดาเนินงาน", MessageBoxButtons.OK)
End Sub
Private Sub dgvProductApp_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvProductApp.CellContentClick
With dgvProductApp
lblwAppId.Text = .Rows.Item(e.RowIndex).Cells(0).Value.ToString
txtID.Text = .Rows.Item(e.RowIndex).Cells(1).Value.ToString
txtName.Text = .Rows.Item(e.RowIndex).Cells(2).Value.ToString
txtPrice.Text = .Rows.Item(e.RowIndex).Cells(3).Value.ToString
txtQty.Text = .Rows.Item(e.RowIndex).Cells(4).Value.ToString
lblSaleTotal.Text = .Rows.Item(e.RowIndex).Cells(5).Value.ToString
txtStatus.Text = .Rows.Item(e.RowIndex).Cells(6).Value.ToString
End With
GroupBox2.Enabled = True
End Sub
Private Sub cmdAddPro_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAddPro.Click
If txtID.Text = String.Empty Then
MsgBox("กรุณาเลือกรหัสพัสดุที่ต้องการสั่งซื้อด้วยนะคะ", MsgBoxStyle.Information, "แจ้งเตือน")
txtQty.Focus()
Exit Sub
End If
If txtQty.Text = String.Empty Then
MsgBox("กรุณากรอกจานวนพัสดุเป็นตัวเลขด้วยนะคะ", MsgBoxStyle.Information, "แจ้งเตือน")
txtQty.Focus()
Exit Sub
End If
Dim total As Single
total = txtPrice.Text * txtQty.Text
lblSaleTotal.Text = total
Call InsertPro()
Call ShowdtWpp()
Call ClearProOrders()
cmdSave.Enabled = True
cmdEdit.Enabled = True
cmdDelete.Enabled = True
End Sub
Private Sub cmdDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDelete.Click
If MsgBox("คุณต้องการลบข้อมูล" & vbCrLf & txtName.Text.Trim & "ใช่หรือไม่", MsgBoxStyle.YesNo & MsgBoxStyle.Question, "ยืนยันการลบข้อมูล") = MsgBoxResult.No Then Exit Sub
Dim sql As String
Dim comedit As New SqlCommand
conn.Open()
sql = "Delete from tbWApprovs_detail where Pasadu_id = '" & Trim(txtID.Text) & "'"
With comedit
.CommandText = sql
.CommandType = CommandType.Text
.Connection = conn
.ExecuteNonQuery()
End With
conn.Close()
MsgBox("ทาการลบข้อมูล" & RTrim(txtName.Text) & "เรียบร้อยแล้วค่ะ")
Call ClearProOrders()
Call ShowdtWpp()
cmdAddPro.Enabled = True
End Sub
Private Sub cmdEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdEdit.Click
If txtQty.Text = String.Empty Then
MsgBox("กรุณากรอกจานวนพัสดุเป็นตัวเลขด้วยนะคะ", MsgBoxStyle.Information, "แจ้งเตือน")
txtQty.Focus()
Exit Sub
End If
Dim total As Single
total = txtPrice.Text * txtQty.Text
lblSaleTotal.Text = total
Dim sql As String
Dim comedit As New SqlCommand
conn.Open()
sql = "Update tbWApprovs_detail set AppQty = '" & Trim(txtQty.Text) & "',AppTotal = '" & Trim(lblSaleTotal.Text) & "'where Pasadu_id = '" & txtID.Text.Trim & "'"
With comedit
.CommandText = sql
.CommandType = CommandType.Text
.Connection = conn
.ExecuteNonQuery()
End With
conn.Close()
Call ClearProOrders()
Call ShowdtWpp()
cmdAddPro.Enabled = True
End Sub
Private Sub cmdExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdExit.Click
'-->ปุ่มปิด
If MessageBox.Show("คุณต้องการออกจากหน้าจอนี้ใช่หรือไม่?", "ข้อความจากระบบ", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
Me.Close()
End If
End Sub
Private Sub txtQty_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtQty.KeyPress
If e.KeyChar < "0" Or e.KeyChar > "9" Then
e.Handled = True
MessageBox.Show("กรุณากรอกจานวนเต็มคะ")
txtQty.Focus()
End If
End Sub
End Class
Load balance : Server 04
