Imports System.Data
Imports System.Data.OleDb
Imports System.Text
Public Class Sale
Dim Conn As New OleDbConnection
Dim da As New OleDbDataAdapter
Dim ds As New DataSet
Dim sb As New StringBuilder
Dim Com As OleDbCommand
Dim dr As OleDbDataReader
Dim IsFind As Boolean
Dim LastSaleId As String
Private Sub btnBrowseCustomer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBrowseCustomer.Click
Dim fAllCustomer As New AllCustomer
fAllCustomer.ShowDialog(Me)
txtcustID.Text = rCustID
txtFullName.Text = rFullName
rCustID = ""
rFullName = ""
End Sub
Private Sub btnBrowseProduct_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBrowseProduct.Click
Dim fAllProduct As New AllProduct
fAllProduct.ShowDialog(Me)
txtProductID.Text = rProductId
txtProductName.Text = rProductName
txtUnitPerPrice.Text = CDec(rUnitperPrice)
txtUnitInStock.Text = CInt(rUnitInStock)
rProductId = ""
rProductName = ""
rUnitperPrice = 0.0
rUnitInStock = 0
End Sub
Private Sub Sale_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
With Conn
If .State = ConnectionState.Open Then .Close()
.ConnectionString = strConn
.Open()
End With
mskSaleDate.Text = Format(Today, "dd/MM/yyyy")
lsvSale.AutoResizeColumns(ColumnHeaderAutoResizeStyle.HeaderSize)
lsvSale.Columns.Add("รหัสสินค้า", 70, HorizontalAlignment.Left)
lsvSale.Columns.Add("ชื่อสินค้า", 170, HorizontalAlignment.Left)
lsvSale.Columns.Add("ราคาต่อหน่วย", 80, HorizontalAlignment.Right)
lsvSale.Columns.Add("จำนวนที่ขาย", 80, HorizontalAlignment.Right)
lsvSale.Columns.Add("สินค้าคงเหลือ", 80, HorizontalAlignment.Right)
lsvSale.Columns.Add("รวมเป็นเงิน", 90, HorizontalAlignment.Right)
lsvSale.View = View.Details
lsvSale.GridLines = True
txtProductID.Text = ""
txtProductName.Text = ""
txtUnitPerPrice.Text = ""
txtUnitSale.Text = ""
End Sub
Sub CalCheckStock()
If txtUnitSale.Text = "" Then
MessageBox.Show("กรุณาป้อนจำนวนสินค้าที่ขายก่อน", "คำเตือน", MessageBoxButtons.OK, MessageBoxIcon.Information)
txtUnitSale.Focus()
txtUnitSale.SelectAll()
Exit Sub
End If
If CSng(txtUnitInStock.Text) < CSng(txtUnitSale.Text) Then
MessageBox.Show("จำนวนสินค้าคงเหลือมีน้อยกว่าจำนวนสินค้าที่ต้องการซื้อ", "คำเตือน", MessageBoxButtons.OK, MessageBoxIcon.Warning)
txtUnitSale.Focus()
txtUnitSale.SelectAll()
Exit Sub
End If
End Sub
Sub CalSale()
Dim i As Integer
Dim Amount As Single = 0.0
Dim VAT As Single = 0.0
Dim Sum As Single = 0.0
For i = 0 To lsvSale.Items.Count - 1
Amount = Amount + CSng(lsvSale.Items(i).SubItems(5).Text)
VAT = Amount * 0.07
Sum = Amount + VAT
Next
lblTotal.Text = Amount.ToString("#,##0.00")
lblVAT.Text = VAT.ToString("#,##0.00")
lblNetTotal.Text = Sum.ToString("#,##0.00")
End Sub
Sub ClearData()
txtProductID.Clear()
txtProductName.Clear()
txtUnitPerPrice.Clear()
txtUnitSale.Clear()
txtUnitInStock.Clear()
txtNewUnitInStock.Clear()
txtTotal.Clear()
End Sub
Sub ClearAllData()
ClearData()
lsvSale.Items.Clear()
txtCash.Clear()
lblVAT.Text = ""
lblChange.Text = ""
lblTotal.Text = ""
lblNetTotal.Text = ""
End Sub
Private Sub CalculateProduct()
Dim i As Integer = 0
Dim tmpUnitperPrice As Integer = 0
Dim tmpUnitSale As Integer = 0
Dim tmpAmount As Integer = 0
Dim VAT As Single
Dim tmpSum As Single = 0
Dim tmpUnitInStock As Integer = 0
Dim tmpNet As Single = 0.0
For i = 0 To lsvSale.Items.Count - 1
tmpUnitperPrice = CInt(lsvSale.Items(i).SubItems(2).Text)
tmpUnitSale = CInt(lsvSale.Items(i).SubItems(3).Text)
tmpAmount = tmpAmount + (tmpUnitperPrice * tmpUnitSale)
VAT = CSng(tmpAmount * 0.07)
tmpSum = tmpAmount + VAT
Next
lblTotal.Text = tmpAmount.ToString("#,##0.00")
lblVAT.Text = VAT.ToString("#,##0.00")
lblNetTotal.Text = tmpSum.ToString("#,##0.00")
End Sub
'ซับรูทีนสำหรับลบรายการในคอนโทรล ListView
Sub RemoveSale()
Dim i As Integer
Dim Amount As Single = 0.0
Dim VAT As Single = 0.0
Dim Sum As Single = 0.0
For i = 0 To lsvSale.SelectedItems.Count - 1
Dim lvi As ListViewItem
lvi = lsvSale.SelectedItems(i)
lsvSale.Items.Remove(lvi)
CalSale() 'เรียกใช้ซับรูทีนเพื่อคำนวณค่าสินค้าใหม่
Next
End Sub
Sub AddSale()
If (txtProductId.Text = "") Or (txtProductName.Text = "") Or (txtUnitperPrice.Text = "") Or (txtUnitSale.Text = "") Then
txtProductId.Focus()
Exit Sub
End If
Dim i As Integer = 0
' นำข้อมูลจากแต่ละคอนโทรลมาใส่ใน SaleData() เพื่อนำไปแสดงในคอนโทรล ListView
Dim TotalSale As Single = 0.0
Dim lvi As ListViewItem
Dim Stock As Integer
TotalSale = CSng(CInt(txtUnitSale.Text) * CInt(txtUnitperPrice.Text))
Stock = CInt(txtUnitInStock.Text) - CInt(txtUnitSale.Text)
Dim SaleData() As String
SaleData = New String() { _
txtProductId.Text, _
txtProductName.Text, _
txtUnitperPrice.Text, _
txtUnitSale.Text, _
Stock, _
TotalSale.ToString("#,##0")}
lvi = New ListViewItem(SaleData)
lsvSale.Items.Add(lvi) ' นำข้อมูลจากคอนโทรลแต่ละตัว ใน SaleData มาใส่ในคอนโทรล ListView
CalSale()
ClearData()
txtProductId.Focus()
End Sub
Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
If txtUnitSale.Text = "" Then
MessageBox.Show("กรุณาป้อนจำนวนสินค้าที่ขายก่อน", "คำเตือน", MessageBoxButtons.OK, MessageBoxIcon.Information)
txtUnitSale.Focus()
txtUnitSale.SelectAll()
Exit Sub
End If
If CInt(txtUnitInStock.Text) < CInt(txtUnitSale.Text) Then
MessageBox.Show("จำนวนสินค้าคงเหลือมีน้อยกว่าจำนวนสินค้าที่ต้องการซื้อ", "คำเตือน", MessageBoxButtons.OK, MessageBoxIcon.Warning)
txtUnitSale.Focus()
txtUnitSale.SelectAll()
Exit Sub
Else
AddSale()
End If
End Sub
Private Sub txtUnitSale_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtUnitSale.KeyDown
If e.KeyCode = Keys.Enter Then
CalCheckStock()
txtNewUnitInStock.Text = CInt(txtUnitInStock.Text) - CInt(txtUnitSale.Text)
'If CDec(txtCash.Text) < CDec(lblNetTotal.Text) Then
' MessageBox.Show("จำนวนเงินที่รับจากลูกค้าน้อยกว่ายอดค่าสินค้าที่ต้องชำระ", "คำเตือน", MessageBoxButtons.OK, MessageBoxIcon.Warning)
' Exit Sub
'End If
txtTotal.Text = CInt(txtUnitSale.Text) * CDec(txtUnitPerPrice.Text)
End If
End Sub
Private Sub txtUnitSale_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtUnitSale.KeyPress
If e.KeyChar < "0" Or e.KeyChar > "9" Then
e.Handled = True
End If
End Sub
Private Sub txtCash_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtCash.KeyPress
If e.KeyChar < "0" Or e.KeyChar > "9" Then
e.Handled = True
End If
End Sub
Private Sub AutoGenerateSaleId()
Dim sql As String = ""
Dim tmpTransID As Integer = 0
sql = "SELECT TOP 1 SaleID FROM Sale ORDER BY SaleID DESC"
Try
Com = New OleDbCommand
With Com
.CommandType = CommandType.Text
.CommandText = sql
.Connection = Conn
dr = .ExecuteReader()
dr.Read()
tmpTransID = CInt(CStr((dr.Item("SaleID"))))
tmpTransID = tmpTransID + 1
LastSaleId = tmpTransID.ToString("0000000")
lblSaleID.Text = LastSaleId
End With
Catch
LastSaleId = "0000001"
lblSaleID.Text = LastSaleId
End Try
dr.Close()
End Sub
Private Sub tsAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsAdd.Click
AutoGenerateSaleId()
End Sub
Private Sub btncal_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btncal.Click
If txtCash.Text = "" Then
txtCash.Focus()
txtCash.SelectAll()
Exit Sub
End If
If CDec(txtCash.Text) < CDec(lblNetTotal.Text) Then
MessageBox.Show("จำนวนเงินที่ได้รับจากลูกค้าน้อยกว่าค่าสินค้า", "คำเตือน", MessageBoxButtons.OK, MessageBoxIcon.Warning)
txtCash.Focus()
Exit Sub
End If
lblChange.Text = CStr(CDec(txtCash.Text) - CDec(lblNetTotal.Text))
End Sub
Private Sub tsSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsSave.Click
If txtcustID.Text = "" Or txtFullName.Text = "" Then
MessageBox.Show("กรุณาป้อนข้อมูลให้ครบก่อน")
Exit Sub
End If
If MessageBox.Show("คุณต้องการบันทึกข้อมูลใช่หรือไม่???", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
If lsvSale.Items.Count = 0 Then
MessageBox.Show("กรุณาป้อนรายการขายสินค้า!!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Information)
txtProductID.Focus()
Exit Sub
End If
Try
'*******************************************************
' INSERT ข้อมูลในตาราง Sale
AutoGenerateSaleId() 'เรียกใช้ซับรูทีนสำหรับสร้าง รหัสการขาย (SaleId) อัตโนมัติ
Dim sql As String = ""
sql = " INSERT INTO Sale(SaleID,CustID,SaleDate,NetTotal)"
sql &= " VALUES ('" & LastSaleId & "',"
sql &= "'" & CStr(txtcustID.Text) & "',"
sql &= "'" & CDate(mskSaleDate.Text) & "',"
sql &= "'" & CDec(lblNetTotal.Text) & "')"
Com = New OleDbCommand
With Com
.CommandType = CommandType.Text
.CommandText = sql
.Connection = Conn
.ExecuteNonQuery()
End With
'*********************************************************
' INSERT ข้อมูลในตาราง SaleDetail
Dim i As Integer = 0
Dim tmpID As String = ""
Dim tmpUnitperPrice As Integer = 0
Dim tmpUnitSale As Integer = 0
Dim tmpAmount As Integer = 0
Dim tmpUnitInStock As Integer = 0
Dim tmpProductId As String = ""
Dim tmpProductName As String = ""
Dim tmpSum As Single = 0
Dim tmpNet As Single = 0.0
Dim tmpTotal As Single = 0.0 'ตัวแปรเก็บรวมเป็นเงิน
Dim tmpStock As Integer
For i = 0 To lsvSale.Items.Count - 1
sql = " INSERT INTO SaleDetail (SaleID,ProductID,UnitSale,Total)"
sql &= " VALUES ('" & LastSaleId & "',"
tmpProductId = CStr(lsvSale.Items(i).SubItems(0).Text)
tmpProductName = CStr(lsvSale.Items(i).SubItems(1).Text)
tmpUnitperPrice = CInt(lsvSale.Items(i).SubItems(2).Text)
tmpUnitSale = CInt(lsvSale.Items(i).SubItems(3).Text)
tmpUnitInStock = CInt(lsvSale.Items(i).SubItems(4).Text)
'tmpSale = tmpUnitperPrice * tmpUnitSale
tmpTotal = CDec(lsvSale.Items(i).SubItems(5).Text)
tmpStock = tmpUnitInStock - tmpUnitSale
'tmpAmount = tmpAmount + (tmpUnitperPrice * tmpUnitSale)
sql &= "'" & tmpProductId & "',"
sql &= "'" & tmpUnitSale & "',"
sql &= "'" & tmpTotal & "')"
Com = New OleDbCommand
With Com
.CommandType = CommandType.Text
.CommandText = sql
.Connection = Conn
.ExecuteNonQuery()
End With
' ส่วนของการ Update สินค้าในสต๊อก
sql = " UPDATE Product SET UnitInStock ='" & tmpStock & "'"
sql &= " WHERE (ProductId ='" & tmpProductId & "')"
With Com
.CommandType = CommandType.Text
.CommandText = sql
.Connection = Conn
.ExecuteNonQuery()
End With
Next
'*********************************************************
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
MessageBox.Show("บันทึกข้อมูลเรียบร้อยแล้ว", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
If MessageBox.Show("คุณต้องการพิมพ์ใบเสร็จหรือไม่ ???", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
Dim frepReceipt As New ReceiptReport
rep_SaleID = LastSaleId
frmReceiptReport.Show()
End If
ClearData()
ClearAllData()
Else
MessageBox.Show("ยกเลิกการบันทึกข้อมูล", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
End Sub
Private Sub btnclear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnclear.Click
ClearAllData()
End Sub
Private Sub tsClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsClear.Click
ClearData()
End Sub
Private Sub tsClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsClose.Click
If MessageBox.Show("คุณต้องการออกจากระบบใช้หรือไม่???", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
Me.Close()
End If
End Sub
End Class
Load balance : Server 03
