This document provides a detailed overview of a fully integrated system which includes a client-server application and a shopping website for an e-Commerce company.
This system consists of 3 main components. The first component is a Powerbuilder 7.0 client-server application designed for internal use for the company employees; the second component is a web-based application specifically designed for the company's customers. The third component is an SQLAnywhere relational database, which serves as the backend data repository. The first and second components serve as front-end applications to the SQLAnywhere database.
The Powerbuilder application is a 2-tiered component. It consists of a front-end GUI interface, with all business logic encapsulated in objects called NonVisual Objects (NVOs) and the SQLAnywhere database as data repository. The Powerbuilder application supports 4 major functionaliy:
The Store Foundation
The store is made up of multiple ASP pages combined with a Visual Basic ActiveX DLL, clswebmall for business rule and data retrieval management. These pages will retrieve and update data in the application database. Table 1 below defines all the pages used in the store and their functions.
Table 1: Web Mall Web Pages
Page
Description
default.asp Home page for the store header.asp Included at the top of most pages on the site. It displays the various product categories available on the site. It also checks if a cookie has been previously stored on the shopper's computer. If a cookie exists, the page will retrieve the shopper ID from the cookie. If no cookie exists, the page will create a shopper ID and store the ID in a session variable. The cookie will then be created on the shopper's computer with the shopper ID and its expiration date. footer.asp Included at the bottom of every page on the site. It provides the navigation structure. additem.asp Adds items to the shopping basket when the shopper selects a product. authenticate.asp Authenticates using the email address and password specify by the shopper before displaying order history. basketsummary.asp Displays the number of items in the shopping basket. beverages.asp Displays the list of beverages. condiments.asp Displays the list of condiments. confections.asp Displays the list of confections. confirmorder.asp Provides a confirmation message and thank you when the shopper has successfully completed an order. It also displays the order number for order status checking. customerservice.asp Provides answers to a list of most commonly asked questions by shoppers. dairyproduct.asp Displays the list of dairy products. emptybasket.asp Empties all items from the shopping basket. global.asa Application level file that is executed each time a new application or session is started. healthbeauty.asp Displays a list of health and beauty products. household.asp Displays a list of household products. login.asp Displays a login page which requires the shopper's email address and password. meat.asp Displays a list of meat products. orderhistory.asp Displays a shopper's order history. orderreceipt.asp Displays order receipt. orderstatus.asp Displays the status of an order, requires shopper to enter the order number and email address. payment.asp Provides data input to capture the shopper's billing information. produce.asp Displays a list of produce. productdetails.asp Displays information on the specific product. quickmeals.asp Displays a list of quick meal products. removeitem.asp Removes an item from the shopping basket. search.asp Provides search capabilities for finding products in the database. searchresults.asp Displays the search results returned by sql query. shipping.asp Provides data input to capture shipping information. shoppingbasket.asp Displays all items added to the shopping basket by the shopper. showorderstatus.asp Gets the order status from the database. updatebasket.asp Updates the shopping basket items with the specified new quantity. validatepayment.asp Validates the payment information entered by the shopper. validateshipping.asp Validates the shipping information entered by the shopper.
Site Architecture
When the shopper visits the website and decides to make an order, the shopping process begins. He or she may begin with browsing through the various product categories, go directly to a particular product category or search for a specific product. The shopper will see the product listings, select a particular product, and add the item to his or her shopping basket. The shopper will then manage his or her shopping basket until it is time to check out. The check out process will begin with collecting and validating the shopper's shipping and billing information. Once the informaton is validated and processed, an order will be generated. An order confirmation page will be displayed and the shopper will be given the order number to track the order status. A shopper will also be able to check his or her order history by using an email address and password.
The clswebmall class is contained in a Visual Basic COM object. The COM object is then compiled into an ActiveX DLL which will be implemented by the ASP pages. Business logic for accessing the database and data manipulation are encapsulated in this 2-tier COM object. Table 2 below defines all the functions or procedures used in the COM object.
Table 2: clswebmall
Functions/Procedures
Description
CalculateShipping Calculates the appropriate shipping rate based on order total. CalculateSubtotal Calculates the subtotal for all items in the shopping basket and updates the shopping basket. CalculateTax Returns the appropriate tax rate based on the order subtotal and the ship-to-state. CheckBasketItemQty Returns the quantity of a specific item in the basket. CreateBasket Creates a shopping basket. DeleteBasket Deletes a particular basket. DeleteBasketItem Deletes a particular item in the basket. DoesUserExist Check if a shopper is a valid user of the system. EmptyBasket Deletes all items in the basket. GetCustID Gets the customer ID. GetNextSequenceKey Gets the last primary key for a table and then increment by 1 to get the next sequence key for the new row. GetOrderStatus Returns the order status for an order. GetSubtotal Returns the order subtotal. InsertBasketItem Inserts a product item into the basket item table, and update the total number of items in the shopping basket. InsertCustomer Inserts a new customer into the customer table and returns the customer ID. InsertOrder Inserts an order into the order table with complete billing and shipping information. InsertPayment Inserts credit card billing info into the payment table. InsertUser Inserts a customer into the app_user table as a valid user, if the customer does not exist in the table. ItemsCountInBasket Returns the total number of items in the basket. LookupDescription Returns the description for a code value. LookupStateCd Returns the numeric state code ID for a state. RetrieveBasketItem Retrieves all the product items from the basket, counts the total number of items in the basket_item table and returns the value. RetrieveCategory Retrieves data from the code_category table given a category code
RetrieveOrders Retrieves the order information from the order table. RetrieveProducts Retrieves the list of products within the same category from the product table. RetrieveReceiptHeader Retrieves the customer's order, billing and shipping information to be displayed in the header of the receipt. RetrieveReceiptItems Retrieves the customer's order details to be displayed in the receipt. RetrieveReceiptTotal Retrieves the order total, shipping cost and tax rate to be displayed in the receipt. RetrieveSpecificProduct Retrieves specific product information given the product ID. SearchProducts Search for a particular product based on the price range entered by the shopper. UpdateBasket Updates the basket table with the shipping, tax and order total information. UpdateBasketItemQty Updates the quantity for a specific product item in the shopping basket. UpdateBasketTotalItems Counts the total number of product items found in the basket_item table and then updates the item total in the shopping basket UpdateCustInfo Updates the customer personal and mailing information; checks the app_user table to see if this customer is a valid user; if not, add this customer into the table. UpdateCustOrderTotal Updates the customer's total ordered amount in the order table. UpdateOrderStatus Updates the order status for an order.
The database platform used in this system is an SQLAnywhere database. This database acts as the backend data repository to support both the internal Powerbuilder application as well as the WebMall. There are 26 tables in the database.