01.
02. For i = 0 To lsvEggList.Items.Count - 1
03. EggId = lsvEggList.Items(i).SubItems(0).Text
04. EggPrice = CDbl(lsvEggList.Items(i).SubItems(1).Text)
05. EggQuan = CInt(lsvEggList.Items(i).SubItems(2).Text)
06. EggTotal = CDbl(lsvEggList.Items(i).SubItems(3).Text)
07. sql.Remove(0, sql.Length)
08. sql = "SELECT * FROM stok WHERE egg_id = @egg_id ORDER BY pick_date ASC"
09. sqlSave = sql.ToString()
10. _cmd = New OleDbCommand(sql, _conn)
11. With _conn
12. _cmd.CommandText = sqlSave
13. _cmd.Parameters.Clear()
14. _cmd.Parameters.Add("egg_id", OleDbType.Char).Value = EggId
15. Dim reader As OleDbDataReader = _cmd.ExecuteReader()
16. If reader.HasRows Then
17. reader.Read()
18. OldAmount = reader("stk_total")
19. StkDate = reader("pick_date")
20. reader.Close()
21.
22. NewAmount = (OldAmount - (EggQuan * 30))
23.
24. If NewAmount <= 0 Then
25.
26. b = OldAmount - NewAmount
27. a = a - b
28.
29. sql.Remove(0, sql.Length)
30. sql = "DELETE * FROM stok WHERE egg_id =@eggid AND pick_date = @date"
31. sqlSave = sql.ToString
32. _cmd = New OleDbCommand(sql, _conn)
33. With _conn
34. _cmd.CommandText = sqlSave
35. _cmd.CommandType = CommandType.Text
36. _cmd.Parameters.Clear()
37.
38. _cmd.Parameters.Add("eggid", OleDbType.Char).Value = EggId
39. _cmd.Parameters.Add("date", OleDbType.Date).Value = StkDate
40. _cmd.ExecuteNonQuery()
41. End With
42.
43. sql.Remove(0, sql.Length)
44. sql = "UPDATE stok SET stk_total = SumPick, pick_date = @date WHERE egg_id = @eggid AND pick_date = @date"
45. sqlSave = sql.ToString
46. _cmd = New OleDbCommand(sql, _conn)
47. With _conn
48. _cmd.CommandText = sqlSave
49. _cmd.CommandType = CommandType.Text
50. _cmd.Parameters.Clear()
51. _cmd.Parameters.Add("SumPick", OleDbType.Integer).Value = a
52. _cmd.Parameters.Add("date", OleDbType.Date).Value = StkDate
53. _cmd.Parameters.Add("eggid", OleDbType.Char).Value = EggId
54. _cmd.ExecuteNonQuery()
55. End With
56.
57. Else
58. sql.Remove(0, sql.Length)
59. sql = "UPDATE stok SET stk_total = @SumPick, pick_date = @date WHERE egg_id = @eggid AND pick_date = @date"
60. sqlSave = sql.ToString
61. _cmd = New OleDbCommand(sql, _conn)
62. With _conn
63. _cmd.CommandText = sqlSave
64. _cmd.CommandType = CommandType.Text
65. _cmd.Parameters.Clear()
66. _cmd.Parameters.Add("SumPick", OleDbType.Integer).Value = NewAmount
67. _cmd.Parameters.Add("date", OleDbType.Date).Value = StkDate
68. _cmd.Parameters.Add("eggid", OleDbType.Char).Value = EggId
69. _cmd.ExecuteNonQuery()
70. End With
71. End If
