Point of Sale in Visual Basic (Part 4)

Previous|Next
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:
  1. Type the Product ID and the Quantity
  2. Validate if the Product exists on the Products Catalog.
    • If the Product does not exists generate an exception (Throw)
  3. 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 en la entrada