Private Sub frommain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.WindowState = FormWindowState.Maximized
lblDate.Text = DateTime.Now.ToLongDateString()
Dim strConn As String
strConn = DBConnString.strConn
Conn = New SqlConnection()
With Conn
If .State = ConnectionState.Open Then .Close()
.ConnectionString = strConn
.Open()
End With
FormatlsvProductList()
Exp.Visible = False
Amount.Visible = False
End Sub
Private Sub FormatlsvProductList()
With lsvProductList
.Columns.Add("รหัสสินค้า", 90, HorizontalAlignment.Left)
.Columns.Add("ชื่อสินค้า", 190, HorizontalAlignment.Left)
.Columns.Add("ราคาขาย", 60, HorizontalAlignment.Right)
.Columns.Add("จำนวน", 50, HorizontalAlignment.Right)
.Columns.Add("ส่วนลด (%)", 60, HorizontalAlignment.Right)
.Columns.Add("รวมเป็นเงิน", 75, HorizontalAlignment.Right)
.Columns.Add("", 0, HorizontalAlignment.Right) ' MeReceived
.Columns.Add("", 0, HorizontalAlignment.Right) ' PaidToSupplier
.Columns.Add("", 0, HorizontalAlignment.Right) ' DCByMember
.View = View.Details
.GridLines = True
End With
End Sub
'ส่วนของ เมนูสตริป เรียกฟอร์มลูก
Private Sub stProAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles stProAdd.Click
fromProductAdd.Show()
End Sub
Private Sub stMatAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles stMatAdd.Click
fromMaterialAdd.Show()
End Sub
Private Sub stUser_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles stUser.Click
fromUserAdd.Show()
End Sub
Private Sub stExpose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles stExpose.Click
fromExpose.Show()
End Sub
Private Sub stProduct_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles stProduct.Click
fromProductStore.Show()
End Sub
Private Sub stMaterial_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles stMaterial.Click
fromMaterialStore.Show()
End Sub
Private Sub stMaterialDown_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles stMaterialDown.Click
fromMaterialDown.Show()
End Sub
Private Sub stBill_Bill_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
fromBill.Show()
End Sub
Private Sub stExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles stExit.Click
'Dim msb As MsgBoxResult
'msb = MessageBox.Show("คุณต้องการออกจากระบบใช่หรือไม่ !!!", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
'If msb = MsgBoxResult.Yes Then
' Application.Exit()
' Me.Close()
'End If
If MessageBox.Show("คุณต้องการออกจากระบบการทำงานใช่หรือไม่ ?", "ออกจากระบบทำงาน ?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
'MessageBox.Show("จบการทำงาน", "ออกจากระบบการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
Application.Exit()
Me.Close()
End If
End Sub
Private Sub txtProId_KeyDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtProId.KeyDown
If txtProId.Text.Trim() = "" Then Exit Sub
If e.KeyCode = Keys.Enter Then
sb = New StringBuilder
sb.Remove(0, sb.Length())
sb.Append("SELECT tb_Product_Store.Pro_ID,tb_Product.Pro_Name,")
sb.Append("tb_Product.Pro_Price,tb_Product_Store.Pro_DateExp ")
sb.Append("FROM tb_Product_Store,tb_Product ")
sb.Append("WHERE (tb_Product_Store.Pro_ID = tb_Product.Pro_Id) AND (tb_Product_Store.Pro_ID = @Pro_ID) ")
sb.Append("AND (tb_Product_Store.Pro_DateExp > '" & Date.Now.AddYears(-543).ToString("MM/dd/yyyy") & "') ")
Dim sqlSale As String
sqlSale = sb.ToString()
Dim DateExp As Date
Dim DateNow As Date = Now
Dim day30 As String
Dim day50 As String
Dim day10 As String
Dim dayNow As String
com = New SqlCommand
With com
.Parameters.Clear()
.Parameters.Add("@Pro_ID", SqlDbType.VarChar).Value = txtProId.Text.Trim()
.CommandText = sqlSale
.CommandType = CommandType.Text
.Connection = Conn
dr = .ExecuteReader
If dr.HasRows Then
With dr
.Read()
DateExp = .GetDateTime(.GetOrdinal("Pro_DateExp"))
name = .GetString(.GetOrdinal("Pro_Name"))
price = .GetDouble(.GetOrdinal("Pro_Price"))
End With
day50 = Date.Today.AddDays(+1).ToString("dd/MM/yy")
day30 = Date.Today.AddDays(+2).ToString("dd/MM/yy")
day10 = Date.Today.AddDays(+3).ToString("dd/MM/yy")
dayNow = DateExp.ToString("dd/MM/yy")
Discount = 0
If day50 = dayNow Then
Discount = 50
ElseIf day30 = dayNow Then
Discount = 30
ElseIf day10 = dayNow Then
Discount = 10
End If
Else
MessageBox.Show("ไม่มีรายชื่อสินค้าตามเงื่อนไขที่คุณระบุ !!!", "ผลการค้นหา", MessageBoxButtons.OK, MessageBoxIcon.Information)
txtProId.SelectAll()
End If
End With
dr.Close()
txtAmount.Focus()
End If
End Sub
Private Sub txtAmount_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtAmount.KeyDown
If txtProId.Text.Trim() = "" Then Exit Sub
If e.KeyCode = Keys.Enter Then
Dim i As Integer = 0
Dim lvi As ListViewItem
Dim ProductID As String = ""
Dim Quantity As Integer = 0
Dim anyData() As String
Dim AmountSum As Integer
Dim sqlDepot As String
sb = New StringBuilder
sb.Remove(0, sb.Length())
sb.Append("SELECT SUM(Store_Order) AS Sum FROM tb_Product_Store ")
sb.Append("WHERE (Pro_Id=@Pro_Id) ")
sb.Append("AND (tb_Product_Store.Pro_DateExp >= '" & Date.Now.AddYears(-543).ToString("MM/dd/yyyy") & "') ")
sqlDepot = sb.ToString()
com = New SqlCommand
With com
.Parameters.Add("@Pro_Id", SqlDbType.NChar).Value = txtProId.Text
.CommandText = sqlDepot
.CommandType = CommandType.Text
.Connection = Conn
.Transaction = tr
dr = .ExecuteReader()
If dr.HasRows Then
With dr
.Read()
AmountSum = .GetInt32(.GetOrdinal("Sum"))
.Close()
End With
End If
End With
If CInt(txtAmount.Text) > AmountSum Then
MessageBox.Show("สินค้ารหัส" & txtProId.Text & "มีเพียง" & AmountSum & "ซึ่งไม่เพียงพอต่อความต้องการ กรุณากรอกข้อมูลใหม่ !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
txtAmount.SelectAll()
Exit Sub
End If
'.Columns.Add("รหัสสินค้า", 90, HorizontalAlignment.Left)
'.Columns.Add("ชื่อสินค้า", 190, HorizontalAlignment.Left)
'.Columns.Add("ราคาขาย", 60, HorizontalAlignment.Right)
'.Columns.Add("จำนวน", 50, HorizontalAlignment.Right)
'.Columns.Add("ส่วนลด", 60, HorizontalAlignment.Right)
'.Columns.Add("ภาษี", 60, HorizontalAlignment.Right)
'.Columns.Add("รวมเป็นเงิน", 75, HorizontalAlignment.Right)
Dim total As Integer
If Discount <> 0 Then
total = (price * CInt(txtAmount.Text)) * (Discount / 100)
Else
total = (price * CInt(txtAmount.Text))
End If
For i = 0 To lsvProductList.Items.Count - 1
ProductID = lsvProductList.Items(i).SubItems(0).Text
Quantity = CInt(lsvProductList.Items(i).SubItems(3).Text)
If txtProId.Text.Trim() = ProductID Then
Quantity = Quantity + CInt(txtAmount.Text)
If Quantity > AmountSum Then
txtAmount.SelectAll()
MessageBox.Show("สินค้ารหัส" & txtProId.Text & "มีเพียง" & AmountSum & "ซึ่งไม่เพียงพอต่อความต้องการ กรุณากรอกข้อมูลใหม่ !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
Exit Sub
End If
lvi = lsvProductList.Items(i)
lsvProductList.Items.Remove(lvi)
anyData = New String() {txtProId.Text.Trim(), name, CStr(price), txtAmount.Text, CStr(Discount), CStr(total)}
lvi = New ListViewItem(anyData)
lsvProductList.Items.Add(lvi)
txtProId.Text = ""
txtAmount.Text = ""
txtProId.Focus()
CalculateNet()
Exit Sub
End If
Next
'*************************************************************************************
anyData = New String() {txtProId.Text.Trim(), name, price, txtAmount.Text, CStr(Discount), CStr(total)}
lvi = New ListViewItem(anyData)
lsvProductList.Items.Add(lvi)
CalculateNet()
txtProId.Text = ""
txtAmount.Text = ""
txtProId.Focus()
End If
End Sub
Private Sub CalculateNet()
Dim i As Integer = 0
Dim NetDC As Double = 0.0
Dim NetVAT As Double = 0.0
Dim Net As Double = 0.0
With lsvProductList
For i = 0 To .Items.Count - 1
Net = Net + CDbl(lsvProductList.Items(i).SubItems(5).Text)
NetVAT = Net * (7 / 100)
Net += NetVAT
Next
End With
lblNetVAT.Text = NetVAT.ToString("#,##0.00")
lblNet.Text = Net.ToString("#,##0")
End Sub
Private Sub txtCash_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtCash.KeyPress
e.Handled = CurrencyTextBox.CurrencyOnly(txtCash, e.KeyChar)
End Sub
Private Sub txtCash_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtCash.TextChanged
If lblNet.Text.Trim() = "" Then lblNet.Text = "0"
If txtCash.Text.Trim() = "" Then txtCash.Text = "0"
If CDbl(txtCash.Text) = 0 Then
lblExchange.Text = "0"
Exit Sub
End If
lblExchange.Text = (CDbl(txtCash.Text) - CDbl(lblNet.Text)).ToString("#,##0.00")
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim i As Integer
Dim ID As String
Dim Price As Double
Dim Amount As Integer
Dim Total As Double
Dim Discount As Integer
Try
Truncate()
'.Columns.Add("รหัสสินค้า", 90, HorizontalAlignment.Left)
'.Columns.Add("ชื่อสินค้า", 190, HorizontalAlignment.Left)
'.Columns.Add("ราคาขาย", 60, HorizontalAlignment.Right)
'.Columns.Add("จำนวน", 50, HorizontalAlignment.Right)
'.Columns.Add("ส่วนลด", 60, HorizontalAlignment.Right)
Dim saleID As String = Now
saleID = Date.Today.ToString("ddMMyy") & Date.Now.ToString("HHmmss")
Dim sqlSave As String
For i = 0 To lsvProductList.Items.Count - 1
ID = lsvProductList.Items(i).SubItems(0).Text
Price = CDbl(lsvProductList.Items(i).SubItems(2).Text)
Amount = CInt(lsvProductList.Items(i).SubItems(3).Text)
Discount = CInt(lsvProductList.Items(i).SubItems(4).Text)
Total = CDbl(lsvProductList.Items(i).SubItems(5).Text)
sb.Remove(0, sb.Length)
sb.Append("INSERT INTO tb_SaleDetail (SaleID,ProductID,Amount,SalePrice,Total,Discount) ")
sb.Append("VALUES (@SaleID,@ProductID,@Amount,@SalePrice,@Total,@Discount)")
sqlSave = sb.ToString()
com = New SqlCommand
With com
.CommandText = sqlSave
.CommandType = CommandType.Text
.Connection = Conn
.Parameters.Clear()
.Parameters.Add("@SaleID", SqlDbType.NChar).Value = saleID
.Parameters.Add("@ProductID", SqlDbType.NChar).Value = ID
.Parameters.Add("@Amount", SqlDbType.Int).Value = Amount
.Parameters.Add("@SalePrice", SqlDbType.Float).Value = Price
.Parameters.Add("@Total", SqlDbType.Float).Value = Total
.Parameters.Add("@Discount", SqlDbType.Int).Value = Discount
.ExecuteNonQuery()
End With
Next
sb.Remove(0, sb.Length)
sb.Append("INSERT INTO tb_Sale (Sale_ID,Sale_Date,Sale_Total,Sale_By) ")
sb.Append("VALUES (@Sale_ID,@Sale_Date,@Sale_Total,@Sale_By)")
sqlSave = sb.ToString()
com = New SqlCommand
With com
.CommandText = sqlSave
.CommandType = CommandType.Text
.Connection = Conn
.Parameters.Clear()
.Parameters.Add("@Sale_ID", SqlDbType.NChar).Value = saleID
.Parameters.Add("@Sale_Date", SqlDbType.DateTime).Value = Today
.Parameters.Add("@Sale_Total", SqlDbType.Float).Value = lblNet.Text
.Parameters.Add("@Sale_By", SqlDbType.NChar).Value = "1" 'fromLogin.User
.ExecuteNonQuery()
End With
MessageBox.Show("ระบบได้ทำการเพื่มข้อมูล เรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
lblNet.Text = "0"
lblNetVAT.Text = "0"
txtProId.Text = ""
txtAmount.Text = ""
lsvProductList.Items.Clear()
FormatlsvProductList()
'If MessageBox.Show("คุณต้องการพิมพ์รายการสินค้าหรือไม่?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
' Dim f As Form
' f = New frmReport(txt_ID.Text, Me)
' f.ShowDialog(Me)
'End If
Catch ex As Exception
MessageBox.Show("เกิดข้อผิดผลาด " & ex.Message, "ข้อผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Warning)
End Try
End Sub
Public Sub Truncate()
Dim COUNT As Integer
Dim AmountSum As Integer
Dim CAm As Integer = 0
Dim CPr As String
Dim sqlSUM As String
Dim sqlNUM As String
Dim dtDepot As DataTable
Dim i As Integer
Dim sqlUpdate As String
For i = 0 To CByte(lsvProductList.Items.Count - 1) 'start for
'--------------------------รับสินค้าและจำนวนสินค้า---------------------------
CPr = lsvProductList.Items(i).SubItems(0).Text
CAm = CInt(lsvProductList.Items(i).SubItems(3).Text)
'---------------------------หาสินค้าที่ต้องการในคลัง------------------------
sb = New StringBuilder
sb.Remove(0, sb.Length())
sb.Append("SELECT SUM(Store_Order) AS Sum,COUNT(*) AS Pro_ID")
sb.Append(" FROM tb_Product_Store")
sb.Append(" WHERE Pro_ID='" & CPr & "'")
sb.Append("AND (Pro_DateExp >= '" & Date.Now.AddYears(-543).ToString("MM/dd/yyyy") & "') ")
sqlSUM = sb.ToString()
com = New SqlCommand(sqlSUM, Conn)
com.Transaction = tr
dr = com.ExecuteReader()
If dr.HasRows Then
With dr
.Read()
AmountSum = .GetInt32(.GetOrdinal("Sum"))
COUNT = .GetInt32(.GetOrdinal("Pro_ID"))
.Close()
End With
End If
'*************************************************************************************
sb.Remove(0, sb.Length())
sb.Append("SELECT Pro_DateExp,Store_Order FROM tb_Product_Store")
sb.Append(" WHERE Pro_Id='" & CPr & "'")
sb.Append("AND (Pro_DateExp >= '" & Date.Now.AddYears(-543).ToString("MM/dd/yyyy") & "') ")
sb.Append(" ORDER BY Pro_DateExp ASC")
sqlNUM = sb.ToString()
Dim bs1 As BindingSource = New BindingSource()
com = New SqlCommand(sqlNUM, Conn)
com.Transaction = tr
dr = com.ExecuteReader()
If dr.HasRows Then
dtDepot = New DataTable
dtDepot.Load(dr)
bs1.DataSource = dtDepot
Exp.DataBindings.Clear()
Amount.DataBindings.Clear()
Exp.DataBindings.Add("Text", bs1, "Pro_DateExp")
Amount.DataBindings.Add("Text", bs1, "Store_Order")
End If
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim Outstanding As Integer
While (CAm <= AmountSum)
'--------------------------------กรณีจำนวนในคลังมากกว่าหรือเท่ากับจำนวนที่ต้องการ------------------------------
Outstanding = CInt(Amount.Text) - CAm
sb = New StringBuilder()
sb.Remove(0, sb.Length())
If Outstanding <= 0 Then
sb.Append("UPDATE tb_Product_Store SET Store_Order='0'")
Else
sb.Append("UPDATE tb_Product_Store SET Store_Order=@Store_Order")
End If
sb.Append(" WHERE (Pro_Id=@Pro_Id) AND (Pro_DateExp=@Pro_DateExp)")
sqlUpdate = sb.ToString()
With com
.CommandText = sqlUpdate
.CommandType = CommandType.Text
.Connection = Conn
.Transaction = tr
.Parameters.Clear()
.Parameters.Add("@Pro_Id", SqlDbType.NChar).Value = CPr
.Parameters.Add("@Store_Order", SqlDbType.Float).Value = Outstanding
.Parameters.Add("@Pro_DateExp", SqlDbType.DateTime).Value = Exp.Text
.ExecuteNonQuery()
End With
If Outstanding < 0 Then
bs1.Position += 1
CAm = -Outstanding
Else
Exit While
End If
'-----------------------------------------------------------------------------------
End While
Exp.Text = ""
Amount.Text = ""
Next
dr.Close()
End Sub
End Class
Private Sub txtAmount_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtAmount.KeyDown
If txtProId.Text.Trim() = "" Then Exit Sub
If e.KeyCode = Keys.Enter Then
Dim i As Integer = 0
Dim lvi As ListViewItem
Dim ProductID As String = ""
Dim Quantity As Integer = 0
Dim anyData() As String
Dim AmountSum As Integer
Dim sqlDepot As String
sb = New StringBuilder
sb.Remove(0, sb.Length())
sb.Append("SELECT SUM(Store_Order) AS Sum FROM tb_Product_Store ")
sb.Append("WHERE (Pro_Id=@Pro_Id) ")
sb.Append("AND (tb_Product_Store.Pro_DateExp >= '" & Date.Now.AddYears(-543).ToString("MM/dd/yyyy") & "') ")
sqlDepot = sb.ToString()
com = New SqlCommand
With com
.Parameters.Add("@Pro_Id", SqlDbType.NChar).Value = txtProId.Text
.CommandText = sqlDepot
.CommandType = CommandType.Text
.Connection = Conn
.Transaction = tr
dr = .ExecuteReader()
If dr.HasRows Then
With dr
.Read()
AmountSum = .GetInt32(.GetOrdinal("Sum"))
.Close()
End With
End If
End With
If CInt(txtAmount.Text) > AmountSum Then
MessageBox.Show("สินค้ารหัส" & txtProId.Text & "มีเพียง" & AmountSum & "ซึ่งไม่เพียงพอต่อความต้องการ กรุณากรอกข้อมูลใหม่ !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
txtAmount.SelectAll()
Exit Sub
Private Sub CalculateNet()
Dim i As Integer = 0
Dim NetDC As Double = 0.0
Dim NetVAT As Double = 0.0
Dim Net As Double = 0.0
With lsvProductList
For i = 0 To .Items.Count - 1
Net = Net + CDbl(lsvProductList.Items(i).SubItems(5).Text)
NetVAT = Net * (7 / 100)
Net += NetVAT
Next
End With
lblNetVAT.Text = NetVAT.ToString("#,##0.00")
lblNet.Text = Net.ToString("#,##0")
End Sub
Private Sub txtCash_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtCash.KeyPress
e.Handled = CurrencyTextBox.CurrencyOnly(txtCash, e.KeyChar)
End Sub
Private Sub txtCash_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtCash.TextChanged
If lblNet.Text.Trim() = "" Then lblNet.Text = "0"
If txtCash.Text.Trim() = "" Then txtCash.Text = "0"
If CDbl(txtCash.Text) = 0 Then
lblExchange.Text = "0"
Exit Sub
End If
lblExchange.Text = (CDbl(txtCash.Text) - CDbl(lblNet.Text)).ToString("#,##0.00")
End Sub
Load balance : Server 02
