Orders and Inventory Management

The database Microland.accdb is maintained by the Microland Computer Warehouse, a mail-order computer-supply company. Table 1 through 3 below show parts of three tables in the database. The table Customers identifies each customer by an ID number and gives, in addition to the name and address, the total amount of purchases during the current year prior to today. The table Inventory identifies each product in stock by an ID number and gives, in addition to its description and price (per unit), the quantity in stock at the beginning of the day. The table Orders gives the orders received today.

Table 1: Customers

Table 2: Inventory

Table 3: Orders

Suppose that it is now the end of the day. Write a program that uses the three tables to do the following two tasks.

(a) Display in a list box the items that are out of stock and those that must be reordered to satisfy today’s orders. See the figure below:

Orders and Inventory Management 1

(b) Display in a list box bills for all customers who ordered during the day. Each bill should show the customer’s name, address, items ordered (with costs), and total cost of the order. See the figure below:

Orders and Inventory Management 2

Hints:

You will first need to place the Microland.accdb (an Access Database) into the bin/Debug folder of your project.
If you do not have Microsoft Office on your computer, click on http://www.microsoft.com/en-us/download/details.aspx?id=23734 to download a driver which will allow you to work with this feature
When you have finished linking the tables to your project, you should have the following objects showing in the form’s component tray: BindingSource1, MICROLANDDataSet, CustomersTablesAdapter, BindingSource2, InventoryTableAdapter, BindingSource3, and OrdersTableAdapter.

Suggested Control Names and Attributes:

Name Property	Text Property	Control Type	Notes
frmMicroland	Microland	Form	Holds Controls
btnStock	Out of Stock Items	Button	Triggers event to display out of stock items which need to be reordered.
btnBills	Bills for Today’s Orders	Button	Triggers event to display bills for all orders that were placed today.
lstOutput		ListBox	Displays either out of stock items or customer bills.

Write the Code:

' Project: Orders and Inventory Management
' Description:  Program uses three databases to track orders, customers and inventory for items.
'     User clicks on "Out of Stock" button to determine which items need to be ordered
'     User clicks on "Bills for Today's Orders" to see a summary of each customer and their order details
'     for the current day

Public Class frmMicroland
    ' Declare global variables
    Structure OutOfStock
        Dim itemNumber As String
        Dim description As String
        Dim startQty As Integer
        Dim numOrdered As Integer
        Dim endQty As Integer
    End Structure
    Dim outOfStockArray() As OutOfStock
    Structure Customer
        Dim customerID As Integer
        Dim name As String
        Dim street As String
        Dim city As String
    End Structure
    Dim customerArray() As Customer
    Structure Order
        Dim customerID As Integer
        Dim qtyOrdered As Integer
        Dim description As String
        Dim pricePerItem As Double
        Dim totalPrice As Double
    End Structure
    Dim orderArray() As Order

    Private Sub frmMicroland_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.OrdersTableAdapter.Fill(Me.MicrolandDataSet1.Orders)
        Me.InventoryTableAdapter.Fill(Me.MicrolandDataSet1.Inventory)
        Me.CustomersTableAdapter.Fill(Me.MicrolandDataSet1.Customers)
    End Sub

    Private Sub btnStock_Click(sender As Object, e As EventArgs) Handles btnStock.Click
        DisplayIntroComments()
        CreateStockArray()
        CombineDuplicateItems()
        DisplayOutOfStock()
    End Sub

    Private Sub btnBills_Click(sender As Object, e As EventArgs) Handles btnBills.Click
        lstOutput.Items.Clear()
        CreateCustomerArray()
        CreateOrderArray()
        DisplayOutput()
    End Sub

    Sub DisplayIntroComments()
        ' Display the first few lines of comments when the "Out of Stock" button is pressed
        lstOutput.Items.Clear()
        lstOutput.Items.Add("Here are the items that are out of")
        lstOutput.Items.Add("inventory or must be reordered.")
        lstOutput.Items.Add("")
        lstOutput.Items.Add("The numbers shown give the")
        lstOutput.Items.Add("minimum reorder quantity required.")
        lstOutput.Items.Add("")
    End Sub

    Sub CreateStockArray()
        ' Create a query from items in Orders and Inventory tables
        Dim query = From order In MicrolandDataSet1.Orders
                    Join item In MicrolandDataSet1.Inventory
                    On order.itemID Equals item.itemID
                    Let itemNumber = order.itemID
                    Let description = item.description
                    Let startQuantity = item.quantity
                    Let numberOrdered = order.quantity
                    Select itemNumber, description, startQuantity, numberOrdered
        ' Populate query into array for manipulation
        ReDim outOfStockArray(query.Count - 1)
        For i = 0 To (query.Count - 1)
            outOfStockArray(i).itemNumber = query(i).itemNumber
            outOfStockArray(i).description = query(i).description
            outOfStockArray(i).startQty = CInt(query(i).startQuantity)
            outOfStockArray(i).numOrdered = CInt(query(i).numberOrdered)
            outOfStockArray(i).endQty = 0
        Next
    End Sub

    Sub CombineDuplicateItems()
        ' Consolidates duplicates for items sold to calculate stock
        For i = 0 To (outOfStockArray.Count - 1)
            For j = 0 To (outOfStockArray.Count - 1)
                If i <> j Then
                    If (outOfStockArray(i).itemNumber = outOfStockArray(j).itemNumber) Then
                        ' combine the qty sold and set the duplicate to 0
                        outOfStockArray(i).numOrdered += outOfStockArray(j).numOrdered
                        outOfStockArray(j).numOrdered = 0
                    End If
                End If
            Next
        Next
    End Sub

    Sub DisplayOutOfStock()
        ' calculate the end quantity of each item and display items which need to be ordered
        Dim numberToOrder As Integer = 0
        For i = 0 To (outOfStockArray.Count - 1)
            outOfStockArray(i).endQty = outOfStockArray(i).startQty - outOfStockArray(i).numOrdered
            If outOfStockArray(i).endQty <= 0 Then
                numberToOrder = -(outOfStockArray(i).endQty)
                lstOutput.Items.Add(outOfStockArray(i).itemNumber & " " & numberToOrder & " " & outOfStockArray(i).description)
            End If
        Next
    End Sub

    Sub CreateCustomerArray()
        ' Create a query of only those customers which placed an order
        Dim query = From customer In MicrolandDataSet1.Customers
                    Join order In MicrolandDataSet1.Orders
                    On customer.custID Equals order.custID
                    Let customerID = customer.custID
                    Let name = customer.name
                    Let street = customer.street
                    Let city = customer.city
                    Select customerID, name, street, city
                    Distinct
        ' Populate query results into array for manipulation
        ReDim customerArray(query.Count - 1)
        For i = 0 To (query.Count - 1)
            customerArray(i).customerID = CInt(query(i).customerID)
            customerArray(i).name = query(i).name
            customerArray(i).street = query(i).street
            customerArray(i).city = query(i).city
        Next
    End Sub

    Sub CreateOrderArray()
        ' Create a query from orders and inventory
        Dim query = From order In MicrolandDataSet1.Orders
                    Join item In MicrolandDataSet1.Inventory
                    On order.itemID Equals item.itemID
                    Let itemNumber = order.itemID
                    Let customerNumber = order.custID
                    Let quantity = order.quantity
                    Let description = item.description
                    Let pricePerItem = item.price
                    Select itemNumber, customerNumber, quantity, description, pricePerItem
        ' Populate query into array for manipulation
        ReDim orderArray(query.Count - 1)
        For i = 0 To (query.Count - 1)
            orderArray(i).customerID = query(i).customerNumber
            orderArray(i).qtyOrdered = CInt(query(i).quantity)
            orderArray(i).description = query(i).description
            orderArray(i).pricePerItem = CDbl(query(i).pricePerItem)
            orderArray(i).totalPrice = orderArray(i).qtyOrdered * orderArray(i).pricePerItem
        Next
    End Sub

    Sub DisplayOutput()
        Dim totalPrice As Double = 0
        For i = 0 To (customerArray.Count - 1)
            totalPrice = 0
            ' Display customer information
            lstOutput.Items.Add(customerArray(i).name)
            lstOutput.Items.Add(customerArray(i).street)
            lstOutput.Items.Add(customerArray(i).city)
            lstOutput.Items.Add("")
            ' Display order details
            For j = 0 To (orderArray.Count - 1)
                If customerArray(i).customerID = orderArray(j).customerID Then
                    lstOutput.Items.Add(CInt(orderArray(j).qtyOrdered) & " " & orderArray(j).description & " " & ((orderArray(j).totalPrice).ToString("C")))
                    totalPrice += CDbl(orderArray(j).totalPrice)
                End If
            Next
            ' Display total cost for current customer
            lstOutput.Items.Add("Total Cost: " & (totalPrice).ToString("C"))
            lstOutput.Items.Add("")
        Next
    End Sub
End Class

Block of Code

Practical Examples for Programmers

Orders and Inventory Management