Sales window (frmSale)
Design the form like the picture:
Declare a variable to store the total:
Dim varTotal As Double = 0
The Sales logic process:
- Type the Product ID and the Quantity
- Validate if the Product exists on the Products Catalog.
- If the Product does not exists generate an exception (Throw)
- Validate if the product exists in the temporal sales list.
- If not exists, add new.
- If exists, update the Quantity
The function to add items to the temporal sales list:
Protected Function AddItem() As Boolean Dim cnn As New OleDbConnection(CnnStr) Dim RowCount As Integer = 0 Try cnn.Open() Dim cmd As New OleDbCommand cmd.Connection = cnn ''Validate if the product exists cmd.CommandText = "SELECT COUNT(*) FROM PRODUCTS " & _ "WHERE ID_PRODUCT=@ID_PRODUCT" cmd.Parameters.Add("@ID_PRODUCT", _ OleDbType.VarChar, 50).Value = txtIdProduct.Text RowCount = CInt(cmd.ExecuteScalar) cmd.Parameters.Clear() ''Clear params If (RowCount = 0) Then Throw (New Exception("The product does not exists")) End If ''Validate if the product is in the sales list cmd.CommandText = "SELECT COUNT(*) " & _ " FROM SALE_DETAIL_TMP " & _ " WHERE ID_PRODUCT=@ID_PRODUCT" & _ " AND USER_LOGIN=@USER_LOGIN" cmd.Parameters.Add("@ID_PRODUCT", _ OleDbType.VarChar, 50).Value = txtIdProduct.Text cmd.Parameters.Add("@USER_LOGIN", _ OleDbType.VarChar, 50).Value = frmLogin.UserLogin RowCount = CInt(cmd.ExecuteScalar) cmd.Parameters.Clear() ''Clear params If (RowCount = 0) Then ''Insert New cmd.CommandText = "INSERT INTO SALE_DETAIL_TMP " & _ " (USER_LOGIN, ID_PRODUCT,QUANTITY," & _ "SALE_PRICE,BUY_PRICE,TAX) " & _ " SELECT @USER_LOGIN, ID_PRODUCT, @QTY, " & _ " SALE_PRICE, BUY_PRICE, TAX " & _ " FROM PRODUCTS WHERE ID_PRODUCT=@ID_PRODUCT" cmd.Parameters.Add("@USER_LOGIN", _ OleDbType.VarChar, 50).Value = frmLogin.UserLogin cmd.Parameters.Add("@QTY", _ OleDbType.Double).Value = txtQuantity.Text cmd.Parameters.Add("@ID_PRODUCT", _ OleDbType.VarChar, 50).Value = txtIdProduct.Text Else ''Edit Quantity cmd.CommandText = "UPDATE SALE_DETAIL_TMP " & _ " SET QUANTITY = QUANTITY + @QTY " & _ " WHERE ID_PRODUCT=@ID_PRODUCTO " & _ " AND USER_LOGIN=@USER_LOGIN " cmd.Parameters.Add("@QTY", OleDbType.Double).Value = txtQuantity.Text cmd.Parameters.Add("@ID_PRODUCT", OleDbType.VarChar, 50).Value = txtIdProduct.Text cmd.Parameters.Add("@USER_LOGIN", OleDbType.VarChar, 50).Value = frmLogin.UserLogin End If cmd.ExecuteNonQuery() Return True Catch ex As Exception Throw (ex) Finally cnn.Close() End Try End Function
The code to cancel the sales is:
Protected Function CancelSale() As Boolean Dim cnn As New OleDbConnection(CnnStr) Try cnn.Open() Dim cmd As New OleDbCommand cmd.Connection = cnn cmd.CommandText = "DELETE FROM SALE_DETAIL_TMP " & _ "WHERE USER_LOGIN=@USER_LOGIN" cmd.Parameters.Add("@USER_LOGIN", OleDbType.VarChar, 50).Value = frmLogin.UserLogin cmd.ExecuteNonQuery() Return True Catch ex As Exception Throw (ex) Finally cnn.Close() End Try End Function
To generate the ListView headers:
Protected Sub Headers() With lvSale .View = View.Details .FullRowSelect = True .GridLines = True .HideSelection = False .Columns.Add("Id", 50) .Columns.Add("Product", 250) .Columns.Add("Qty", 50, HorizontalAlignment.Right) .Columns.Add("Price", 50, HorizontalAlignment.Right) .Columns.Add("Total", 80, HorizontalAlignment.Right) End With End Sub
To display the temporal sale items:
Protected Sub DisplaySale() Dim cnn As New OleDbConnection(CnnStr) varTotal = 0 Try cnn.Open() Dim cmd As New OleDbCommand cmd.Connection = cnn cmd.CommandText = "SELECT P.ID_PRODUCT, " & _ "P.PRODUCT, T.QUANTITY, T.SALE_PRICE," & _ "(T.QUANTITY * T.SALE_PRICE) AS TOTAL " & _ " FROM SALE_DETAIL_TMP T, PRODUCTS P " & _ " WHERE T.ID_PRODUCT=P.ID_PRODUCT " & _ " AND T.USER_LOGIN=@USER_LOGIN" cmd.Parameters.Add("@USER_LOGIN", OleDbType.VarChar, 50).Value = frmLogin.UserLogin Dim dr As OleDbDataReader = cmd.ExecuteReader Dim i As Integer = 0 lvSale.Items.Clear() While (dr.Read()) With lvSale .Items.Add(dr("ID_PRODUCT").ToString()) .Items(i).SubItems.Add(dr("PRODUCT").ToString()) .Items(i).SubItems.Add(String.Format("{0:N}", dr("QUANTITY"))) .Items(i).SubItems.Add(String.Format("{0:C}", dr("SALE_PRICE"))) .Items(i).SubItems.Add(String.Format("{0:C}", dr("TOTAL"))) End With varTotal += CDbl(dr("TOTAL")) i += 1 End While dr.Close() lblTotal.Text = String.Format("{0:C}", varTotal) Catch ex As Exception Throw (ex) Finally cnn.Close() End Try End Sub
The "Add" button:
Private Sub btnAdd_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnAdd.Click Try If (AddItem()) Then DisplaySale() txtQuantity.Text = "1" txtIdProduct.Text = "" txtIdProduct.Focus() End If Catch ex As Exception MessageBox.Show(ex.Message, "System Information", MessageBoxButtons.OK, MessageBoxIcon.Error) End Try End Sub
The "Cancel" button:
Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click Try If (CancelSale()) Then lvSale.Items.Clear() End If Catch ex As Exception MessageBox.Show(ex.Message, "System Information", MessageBoxButtons.OK, MessageBoxIcon.Error) End Try End Sub
The "Payment" button:
Private Sub btnPayment_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPayment.Click If (lvSale.Items.Count > 0) Then Dim frm As New frmPayment frm.TotalSale = varTotal frm.StartPosition = FormStartPosition.CenterScreen frm.ShowInTaskbar = False frm.ShowDialog() If (frm.Success) Then lvSale.Items.Clear() End If Else MessageBox.Show("No items", "System Information", MessageBoxButtons.OK, MessageBoxIcon.Error) End If End Sub
The "Form_Load" method:
Private Sub frmSale_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Try Headers() DisplaySale() Catch ex As Exception MessageBox.Show(ex.Message, "System Information", _ MessageBoxButtons.OK, MessageBoxIcon.Error) End Try End Sub
It is the end of the Sales process. After we do the reports using the Visual Studio Reporting options (rdlc reports).
Thanks...
No hay comentarios:
Publicar un comentario