001.Private Sub tsSave_Click(sender As Object, e As EventArgs) Handles tsSave.Click
002. If ListView1.Items.Count = 0 Then
003. MessageBox.Show("ไม่ปรากฏรายการขายใด ๆ ให้บันทึกลงฐานข้อมูล", "ผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Error)
004. Exit Sub
005. End If
006.
007. If MessageBox.Show("ต้องการบันทึกรายการขายสินค้า?", "ยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = DialogResult.Yes Then
008. tr = Conn.BeginTransaction()
009.
010. For i = 0 To ListView1.Items.Count - 1
011. sb = New StringBuilder
012.
013.
014. sb.Append("INSERT INTO AssistSaleDetail(SaleID, ProductID, Price, Qty, TotalPrice) ")
015. sb.Append(" VALUES (@SaleID, @ProductID, @Price, @Qty, @TotalPrice) ")
016. sql = sb.ToString()
017. Command = New SqlCommand
018. With Command
019. .CommandText = sql
020. .CommandType = CommandType.Text
021. .Connection = Conn
022. .Transaction = tr
023. .Parameters.Clear()
024. .Parameters.Add("@SaleID", SqlDbType.VarChar).Value = txtSaleid.Text
025. .Parameters.Add("@ProductID", SqlDbType.VarChar).Value = CStr(ListView1.Items(i).SubItems(0).Text)
026. .Parameters.Add("@Price", SqlDbType.Decimal).Value = CDec(ListView1.Items(i).SubItems(4).Text)
027. .Parameters.Add("@Qty", SqlDbType.Int).Value = CInt(ListView1.Items(i).SubItems(6).Text)
028. .Parameters.Add("@TotalPrice", SqlDbType.Decimal).Value = CDec(ListView1.Items(i).SubItems(7).Text)
029. .ExecuteNonQuery()
030. End With
031.
032.
033.
034. sb.Remove(0, sb.Length)
035. sb.Append("SELECT * FROM AssistStock")
036. sb.Append(" WHERE ProductID=@ProductID")
037. sql = sb.ToString()
038.
039. Dim OldQtyStock As Integer = 0
040. Dim NewQtyStock As Integer = 0
041. Dim tmpProductid As String
042. Dim qtySale As Integer
043.
044. With Command
045. .CommandText = sql
046. tmpProductid = CStr(ListView1.Items(i).SubItems(0).Text)
047. .Parameters.Clear()
048. .Parameters.Add("@ProductID", SqlDbType.VarChar).Value = tmpProductid
049. dr = .ExecuteReader()
050. If dr.HasRows Then
051. dr.Read()
052. OldQtyStock = dr.GetInt32(dr.GetOrdinal("Stock"))
053. dr.Close()
054. End If
055. qtySale = CInt(ListView1.Items(i).SubItems(6).Text)
056.
057. NewQtyStock = OldQtyStock - qtySale
058. dr.Close()
059. sb.Remove(0, sb.Length)
060. sb.Append("UPDATE AssistStock")
061. sb.Append(" SET Stock=@Stock")
062. sb.Append(" WHERE (ProductID=@ProductID)")
063. sql = sb.ToString()
064.
065. With Command
066. .CommandText = sql
067. .Parameters.Clear()
068. .Parameters.Add("@Stock", SqlDbType.Int).Value = NewQtyStock
069. .Parameters.Add("@ProductID", SqlDbType.VarChar).Value = tmpProductid
070. .ExecuteNonQuery()
071. End With
072.
073. End With
074. Next
075.
076.
077. txtQty.Text = Intnum
078. sb.Remove(0, sb.Length)
079. sb.Append("INSERT INTO AssistSale (SaleID, SaleDate, UserName, CategoryName, Brand, Gen, Total, Discount, Net) ")
080. sb.Append(" VALUES (@SaleID, @SaleDate, @UserName, @CategoryName, @Brand, @Gen, @Total, @Discount, @Net ) ")
081. sql = sb.ToString()
082.
083. Command = New SqlCommand
084. With Command
085. .CommandText = sql
086. .CommandType = CommandType.Text
087. .Connection = Conn
088. .Transaction = tr
089. .Parameters.Clear()
090. .Parameters.Add("@SaleID", SqlDbType.VarChar).Value = txtSaleid.Text
091. .Parameters.Add("@SaleDate", SqlDbType.Date).Value = dtpDateSale.Value
092. .Parameters.Add("@UserName", SqlDbType.VarChar).Value = LblEmp.Text
093. .Parameters.Add("@CategoryName", SqlDbType.VarChar).Value = txtCategoryName.Text
094. .Parameters.Add("@Brand", SqlDbType.VarChar).Value = txtBrand.Text
095. .Parameters.Add("@Gen", SqlDbType.VarChar).Value = txtGen.Text
096. .Parameters.Add("@Total", SqlDbType.Decimal).Value = CDbl(LblTotal.Text)
097. .Parameters.Add("@Discount", SqlDbType.Decimal).Value = CDbl(txtDiscount.Text)
098. .Parameters.Add("@Net", SqlDbType.Decimal).Value = CDbl(LblNet.Text)
099. .ExecuteNonQuery()
100. End With
101.
102. tr.Commit()
103. MessageBox.Show("บันทึกรายการขายสินค้าเรียบร้อยแล้ว", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
104.
105.
106. End If
107.
108. ClearData()
109. ClearProductData()
110. ListView1.Items.Clear()
111. LockButton()
112. LockInputControl()
113. End Sub
