Option Explicit On
Option Strict On
Imports System.Data
Imports System.Data.OleDb
Imports System.Drawing.Imaging
Imports System.Drawing.Printing
Public Class frmSale
Dim strConn As String
Dim Conn As New OleDbConnection
Dim da As OleDbDataAdapter
Dim ds As New DataSet
Dim strSQL As String
Dim Check As Boolean
Dim LastRecord As Long
Dim dblSubTotal, dblRecieve, dblChange As Double
Dim IsFindProduct, IsFindMember, IsFindSale, IsFindSaleDetail As Boolean
'----------------------ตัวแปรสำหรับพิมพ์ใบเสร็จ----------------------
Dim lngO_ID As Long = 0
Dim UseFont As New Font("MS Sans Serif", 10)
Dim prDoc As New PrintDocument()
Dim prDlg As New PrintDialog()
Private Sub AddEdit()
cmdAdd.Enabled = False
cmdSave.Enabled = True
cmdCancel.Enabled = True
txtM_ID.Enabled = True
txtP_ID.Enabled = True
cmdAddList.Enabled = True
End Sub
Private Sub SaveCancel()
cmdAdd.Enabled = True
cmdSave.Enabled = False
cmdCancel.Enabled = False
txtM_ID.Enabled = False
txtP_ID.Enabled = False
cmdAddList.Enabled = False
End Sub
Private Sub ClearAllData()
lblS_ID.Text = ""
txtM_ID.Text = ""
lblMName.Text = ""
txtP_ID.Text = ""
lblName.Text = ""
lblPrice.Text = ""
txtQty.Text = ""
lblTotal.Text = ""
lblNet.Text = "0.00"
grdOrder.DataSource = Nothing
End Sub
Private Function CheckList() As Boolean
Check = False
strSQL = "SELECT * FROM Sale_Detail WHERE(S_ID = " & CInt(lblS_ID.Text) & " AND P_ID = '" & txtP_ID.Text & "')"
da = New OleDbDataAdapter(strSQL, Conn)
da.Fill(ds, "List")
If ds.Tables("List").Rows.Count <> 0 Then
Return True
ds.Tables("List").Clear()
Else
Return False
End If
End Function
Private Function CheckDataAdd() As Boolean
Check = False
If lblPrice.Text = "" Or txtQty.Text = "" Or lblTotal.Text = "" Then
MessageBox.Show("กรุณากรอกข้อมูลให้ครบ ", "ข้อมูลไม่ครบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
Return False
Else
Return True
End If
End Function
Private Function CheckDataSave() As Boolean
Check = False
If grdOrder.Rows.Count < 1 Then
MessageBox.Show("กรุณาเลือกสินค้า ", "ข้อมูลไม่ครบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
Return False
Else
Return True
End If
End Function
Private Sub FormatGrd()
With grdOrder
If .RowCount <> 0 Then
.Columns(0).HeaderText = "รหัส"
.Columns(1).HeaderText = "รายการ"
.Columns(2).HeaderText = "ราคา"
.Columns(3).HeaderText = "จำนวน"
.Columns(4).HeaderText = "รวม"
.Columns(0).Width = 60
.Columns(1).Width = 180
.Columns(2).Width = 60
.Columns(3).Width = 50
.Columns(4).Width = 80
End If
End With
End Sub
Private Sub ShowMember()
If txtM_ID.Text = "" Then
txtP_ID.Focus()
Exit Sub
End If
strSQL = "SELECT * FROM Member WHERE(M_ID = '" & txtM_ID.Text & "')"
da = New OleDbDataAdapter(strSQL, Conn)
If IsFindMember = True Then
ds.Tables("Member").Clear()
End If
da.Fill(ds, "Member")
If ds.Tables("Member").Rows.Count <> 0 Then
lblMName.Text = CStr(ds.Tables("Member").Rows(0).Item("Prefix"))
lblMName.Text &= CStr(ds.Tables("Member").Rows(0).Item("Name"))
lblMName.Text &= " "
lblMName.Text &= CStr(ds.Tables("Member").Rows(0).Item("Surname"))
txtP_ID.Focus()
IsFindMember = True
Else
MessageBox.Show("ไม่พบรหัสสมาชิกนี้ ", "สมาชิก", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
txtM_ID.SelectAll()
txtM_ID.Focus()
End If
End Sub
Private Sub ShowProduct()
strSQL = "SELECT * FROM Product WHERE(P_ID = '" & txtP_ID.Text & "')"
da = New OleDbDataAdapter(strSQL, Conn)
If IsFindProduct = True Then
ds.Tables("Product").Clear()
End If
da.Fill(ds, "Product")
If ds.Tables("Product").Rows.Count <> 0 Then
lblName.Text = CStr(ds.Tables("Product").Rows(0).Item("Name"))
lblPrice.Text = CStr(ds.Tables("Product").Rows(0).Item("Price"))
txtQty.Text = "1"
txtQty.Focus()
IsFindProduct = True
Else
MessageBox.Show("ไม่พบรหัสสินค้านี้ ", "สินค้า", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
txtP_ID.SelectAll()
txtP_ID.Focus()
End If
End Sub
Private Sub ShowSale_Detail()
strSQL = "SELECT Sale_Detail.P_ID, Product.Name, Sale_Detail.Price, Sale_Detail.Qty, Sale_Detail.Total FROM Product INNER JOIN Sale_Detail ON Product.P_ID = Sale_Detail.P_ID WHERE(S_ID = " & CInt(lblS_ID.Text) & ")"
da = New OleDbDataAdapter(strSQL, Conn)
If IsFindSaleDetail = True Then
ds.Tables("Sale_Detail").Clear()
End If
da.Fill(ds, "Sale_Detail")
If ds.Tables("Sale_Detail").Rows.Count <> 0 Then
grdOrder.DataSource = ds.Tables("Sale_Detail")
FormatGrd()
IsFindSaleDetail = True
End If
End Sub
Private Sub frmSale_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
gpoMoney.Visible = False
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Community\Database\Community.mdb;Jet OLEDB:Database Password=;"
With Conn
If .State = ConnectionState.Open Then .Close()
.ConnectionString = strConn
.Open()
End With
SaveCancel()
AddHandler prDoc.PrintPage, New PrintPageEventHandler(AddressOf Me.StringToPrint_Print)
prDoc.DocumentName = "ใบเสร็จรับเงิน"
prDlg.Document = prDoc
End Sub
Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAdd.Click
AddEdit()
ClearAllData()
dtpDate.Value = Date.Today
strSQL = "SELECT * FROM Sale"
da = New OleDbDataAdapter(strSQL, Conn)
If IsFindSale = True Then
ds.Tables("Sale").Clear()
End If
da.Fill(ds, "Sale")
If ds.Tables("Sale").Rows.Count <> 0 Then
LastRecord = CLng(ds.Tables("Sale").Rows(ds.Tables("Sale").Rows.Count - 1).Item("S_ID"))
IsFindSale = True
Else
LastRecord = 0
End If
LastRecord += 1
lblS_ID.Text = CStr(LastRecord)
txtM_ID.Focus()
End Sub
Private Sub txtQty_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtQty.KeyPress
If e.KeyChar = Chr(13) Then
On Error GoTo errDup
Dim comSave As New OleDbCommand
If CheckDataAdd() = True Then
If CheckList() = True Then
strSQL = "UPDATE Sale_Detail SET Qty = (Qty + " & CInt(txtQty.Text) & "),Total = (Total + " & CLng(lblTotal.Text) & " ) WHERE(S_ID = " & CInt(lblS_ID.Text) & " AND P_ID = '" & txtP_ID.Text & "')"
With comSave
.CommandType = CommandType.Text
.CommandText = strSQL
.Connection = Conn
.ExecuteNonQuery()
End With
Else
strSQL = "INSERT INTO Sale_Detail(S_ID,P_ID,Price,Qty,Total) VALUES(@S_ID,@P_ID,@Price,@Qty,@Total)"
With comSave
.Parameters.Clear()
.Parameters.Add("@S_ID", SqlDbType.Int).Value = lblS_ID.Text
.Parameters.Add("@P_ID", SqlDbType.Text).Value = txtP_ID.Text
.Parameters.Add("@Price", SqlDbType.Float).Value = lblPrice.Text
.Parameters.Add("@Qty", SqlDbType.Int).Value = txtQty.Text
.Parameters.Add("@Total", SqlDbType.Float).Value = lblTotal.Text
.CommandType = CommandType.Text
.CommandText = strSQL
.Connection = Conn
.ExecuteNonQuery()
End With
End If
txtP_ID.Text = ""
lblName.Text = ""
lblPrice.Text = ""
txtQty.Text = ""
lblTotal.Text = ""
ShowSale_Detail()
SubTotal()
txtP_ID.Focus()
End If
Exit Sub
errDup:
MessageBox.Show("เกิดข้อผิดพลาด กรณาตรวจสอบข้อมูลอีกครั้ง " & Environment.NewLine & "1. อาจเกิดจากการระบุรหัสซ้ำ" & Environment.NewLine & "2. หรืออาจจะระบุชนิดข้อมูลผิดพลาด" & Environment.NewLine & "กรุณาตรวจสอบข้อมูลอีกครั้ง ก่อนทำการบันทึก", "ผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
End If
End Sub
Private Sub txtQty_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtQty.TextChanged
On Error Resume Next
Dim dblPrice As Double
Dim intQty As Integer
Dim dblTotal As Double
dblPrice = 0
intQty = 0
dblTotal = 0
dblPrice = CLng(lblPrice.Text)
intQty = CInt(txtQty.Text)
dblTotal = dblPrice * intQty
lblTotal.Text = CStr(dblTotal)
End Sub
Private Sub cmdAddList_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAddList.Click
On Error GoTo errDup
Dim comSave As New OleDbCommand
If CheckDataAdd() = True Then
If CheckList() = True Then
strSQL = "UPDATE Sale_Detail SET Qty = (Qty + " & CInt(txtQty.Text) & "),Total = (Total + " & CLng(lblTotal.Text) & " ) WHERE(S_ID = " & CInt(lblS_ID.Text) & " AND P_ID = '" & txtP_ID.Text & "')"
With comSave
.CommandType = CommandType.Text
.CommandText = strSQL
.Connection = Conn
.ExecuteNonQuery()
End With
Else
strSQL = "INSERT INTO Sale_Detail(S_ID,P_ID,Price,Qty,Total) VALUES(@S_ID,@P_ID,@Price,@Qty,@Total)"
With comSave
.Parameters.Clear()
.Parameters.Add("@S_ID", SqlDbType.Int).Value = lblS_ID.Text
.Parameters.Add("@P_ID", SqlDbType.Text).Value = txtP_ID.Text
.Parameters.Add("@Price", SqlDbType.Float).Value = lblPrice.Text
.Parameters.Add("@Qty", SqlDbType.Int).Value = txtQty.Text
.Parameters.Add("@Total", SqlDbType.Float).Value = lblTotal.Text
.CommandType = CommandType.Text
.CommandText = strSQL
.Connection = Conn
.ExecuteNonQuery()
End With
End If
txtP_ID.Text = ""
lblName.Text = ""
lblPrice.Text = ""
txtQty.Text = ""
lblTotal.Text = ""
ShowSale_Detail()
SubTotal()
txtP_ID.Focus()
End If
Exit Sub
errDup:
MessageBox.Show("เกิดข้อผิดพลาด กรณาตรวจสอบข้อมูลอีกครั้ง " & Environment.NewLine & "1. อาจเกิดจากการระบุรหัสซ้ำ" & Environment.NewLine & "2. หรืออาจจะระบุชนิดข้อมูลผิดพลาด" & Environment.NewLine & "กรุณาตรวจสอบข้อมูลอีกครั้ง ก่อนทำการบันทึก", "ผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
End Sub
Private Sub SubTotal()
Dim Num, i As Integer
dblSubTotal = 0
strSQL = "SELECT Total FROM Sale_Detail WHERE(S_ID = " & CInt(lblS_ID.Text) & " )"
da = New OleDbDataAdapter(strSQL, Conn)
da.Fill(ds, "SubTotal")
If ds.Tables("SubTotal").Rows.Count <> 0 Then
Num = ds.Tables("SubTotal").Rows.Count - 1
For i = 0 To Num
dblSubTotal = dblSubTotal + CDbl(ds.Tables("SubTotal").Rows(i).Item(0).ToString)
Next
lblNet.Text = Format(dblSubTotal, "#,###.00")
ds.Tables("SubTotal").Clear()
End If
End Sub
Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
On Error GoTo errDup
Dim comSave As New OleDbCommand
Dim comUpdate As New OleDbCommand
Dim NumRows, i As Integer
NumRows = grdOrder.Rows.Count - 1
If CheckDataSave() = True Then
strSQL = "INSERT INTO Sale(S_ID,Date,Net,M_ID)VALUES(@S_ID,@Date,@Net,@M_ID)"
With comSave
.Parameters.Clear()
.Parameters.Add("@S_ID", SqlDbType.Int).Value = lblS_ID.Text
.Parameters.Add("@Date", SqlDbType.DateTime).Value = dtpDate.Value
.Parameters.Add("@Net", SqlDbType.Float).Value = lblNet.Text
If txtM_ID.Text = "" Then
.Parameters.Add("@M_ID", SqlDbType.Int).Value = 0
Else
.Parameters.Add("@M_ID", SqlDbType.Int).Value = txtM_ID.Text
End If
.CommandType = CommandType.Text
.CommandText = strSQL
.Connection = Conn
.ExecuteNonQuery()
End With
For i = 0 To NumRows
strSQL = "UPDATE Product SET Stock = Stock - @Stock WHERE(P_ID = @P_ID)"
With comUpdate
.Parameters.Clear()
.Parameters.Add("@Stock", SqlDbType.Int).Value = grdOrder.Rows(i).Cells(3).Value
.Parameters.Add("@P_ID", SqlDbType.VarChar).Value = grdOrder.Rows(i).Cells(0).Value.ToString()
.CommandType = CommandType.Text
.CommandText = strSQL
.Connection = Conn
.ExecuteNonQuery()
End With
Next
'MessageBox.Show("บันทึกลงฐานข้อมูลเรียบร้อยแล้ว ", "บันทึก", MessageBoxButtons.OK, MessageBoxIcon.Information)
gpoMoney.Visible = True
lblSum.Text = Format(dblSubTotal, "#,##0.00")
lngO_ID = CLng(lblS_ID.Text)
txtRecieve.Text = ""
txtRecieve.Focus()
End If
Exit Sub
errDup:
MessageBox.Show("เกิดข้อผิดพลาด กรณาตรวจสอบข้อมูลอีกครั้ง " & Environment.NewLine & "1. อาจเกิดจากการระบุรหัสซ้ำ" & Environment.NewLine & "2. หรืออาจจะระบุชนิดข้อมูลผิดพลาด" & Environment.NewLine & "กรุณาตรวจสอบข้อมูลอีกครั้ง ก่อนทำการบันทึก", "ผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
End Sub
Private Sub cmdCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCancel.Click
Dim comDelete As New OleDbCommand
strSQL = "DELETE Sale_Detail WHERE(S_ID = " & lblS_ID.Text & ")"
With comDelete
.CommandType = CommandType.Text
.CommandText = strSQL
.Connection = Conn
.ExecuteNonQuery()
End With
SaveCancel()
ClearAllData()
End Sub
Private Sub StringToPrint_Print(ByVal sender As Object, ByVal e As PrintPageEventArgs)
Dim NumRow, i, Lines As Integer
i = 0
NumRow = grdOrder.Rows.Count - 1
Lines = 260
AnyString(e.Graphics, "ระบบร้านค้า", 100, 60)
AnyString(e.Graphics, ".......................................", 20, 80)
AnyString(e.Graphics, "ใบเสร็จรับเงิน", 110, 120)
AnyString(e.Graphics, "เลขที่เอกสาร : " & CStr(lngO_ID), 10, 140)
AnyString(e.Graphics, "วันที่ออกใบเสร็จ : " & Date.Today, 10, 160)
If txtM_ID.Text = "" Then
AnyString(e.Graphics, "ลูกค้า : ลูกค้าทั่วไป", 10, 180)
Else
AnyString(e.Graphics, "ลูกค้า : " & lblMName.Text, 10, 180)
End If
AnyString(e.Graphics, "-----------------------------------------------------------", 10, 200)
AnyString(e.Graphics, "รายการ ราคา จำนวน รวม", 10, 220)
AnyString(e.Graphics, "-----------------------------------------------------------", 10, 240)
For i = 0 To NumRow
AnyString(e.Graphics, grdOrder.Rows.Item(i).Cells(1).Value.ToString(), 10, Lines)
AnyString(e.Graphics, grdOrder.Rows.Item(i).Cells(2).Value.ToString(), 170, Lines)
AnyString(e.Graphics, grdOrder.Rows.Item(i).Cells(3).Value.ToString(), 210, Lines)
AnyString(e.Graphics, grdOrder.Rows.Item(i).Cells(4).Value.ToString(), 250, Lines)
Lines = Lines + 20
Next
AnyString(e.Graphics, "-----------------------------------------------------------", 10, Lines)
AnyString(e.Graphics, "รวมทั้งสิ้น : " & Format(dblSubTotal, "#,##0.00") & ".-", 160, Lines + 20)
AnyString(e.Graphics, "-----------------------------------------------------------", 10, Lines + 40)
AnyString(e.Graphics, "รับเงินมา : " & Format(dblRecieve, "#,##0.00") & ".-", 160, Lines + 60)
AnyString(e.Graphics, "เงินทอน : " & Format(dblChange, "#,##0.00") & ".-", 161, Lines + 80)
AnyString(e.Graphics, "ขอบคุณ(Thank You)", 90, Lines + 120)
End Sub
Private Sub AnyString(ByVal g As Graphics, ByVal printString As String, ByVal xPos As Integer, ByVal yPos As Integer)
Dim anyPoint As New PointF(xPos, yPos)
g.DrawString(printString, UseFont, Brushes.Black, anyPoint)
End Sub
Private Sub txtRecieve_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtRecieve.KeyPress
If e.KeyChar = Chr(13) Then
If dblRecieve < dblSubTotal Then
MessageBox.Show("เงินรับมาน้อยกว่ายอดรวม กรุณาตรวจสอบ ", "ผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
Exit Sub
End If
gpoMoney.Visible = False
prDoc.Print()
SaveCancel()
ClearAllData()
End If
End Sub
Private Sub txtRecieve_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtRecieve.TextChanged
On Error Resume Next
dblRecieve = CDbl(txtRecieve.Text)
dblChange = dblRecieve - dblSubTotal
lblChange.Text = Format(dblChange, "#,##0.00")
End Sub
Private Sub txtM_ID_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtM_ID.KeyPress
If e.KeyChar = Chr(13) Then
ShowMember()
End If
End Sub
Private Sub txtP_ID_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtP_ID.KeyPress
If e.KeyChar = Chr(13) Then
ShowProduct()
End If
End Sub
Private Sub Label10_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label10.Click
End Sub
End Class
Load balance : Server 04
