Syllabus Schedule Project Labs Canvas Server Access CSS Tutorial Assignment 1 Assignment 2 Assignment 3 Assignment 4 Assignment 5 Assignment 6 Assignment 7 Assignment 8 Assignment 9

Assignment 09 - Checkout II and History

The customer information was written to the database in A08. This assignment writes order information to the database, displays a order confirmation page, sends a confirmation email, and adds a history page.

  1. tblOrders music/OrderConfirmation (image) - With the customer information written to the database the next step is to write the order information.
    1. Database: There is a one-to-many relationship between orders and order items. Add two new tables to the Music database: tblOrders and tblOrderItems. The fields are shown in the images. OrderID is autonumber. (note: these tables are not in third normal form, which would utilize another table for product details with primary key ASIN. For the sake of simplicity we will use a slightly de-normalized structure.)
    2. Models: Create two new models: musicOrderModel and MusicOrderItemModel. Their properties match the corresponding database tables.
    3. MusicOrderRepository: Create a method WriteOrder(MusicOrderModel orderModel) that writes to tblOrders and returns OrderId. The method InsertCustomer is a good template. Sql Server's GetDate() function is a convenient way to write OrderDate.
    4. tblOrderItems MusicOrderRepository needs to uses SessionId. Add a using statement for namespace "System.Web". See MusicCartRepository for Session syntax.
    5. Create another method WriteOrderItems(int OrderId) to write orderItems to the database. A single SQL statement can be used to copy item information from tblCart to tblOrderItems. The sql statement uses a Select statement nested inside of an Insert Into statement (See "sql insert sub-query"). The select statement retrieves the relevant records from tblCart and the Insert statement inserts them directly into tblOrderItems. The following statement shows the syntax and uses two parameters: @orderID and @sessionID. Parameters are used for @orderId and @sessionId. See "RemoveFromCart" in MusicCartRepository for syntax.

      string sql = "INSERT INTO tblOrderItems (OrderID, ASIN, Qty, Title, Artist, Price) " +
      "SELECT @orderID, ASIN, Qty, Title, Artist, Price " +
      "FROM tblCart " +
      "WHERE SessionID= @sessionID ";

    6. Controller: The HttpPost checkout handler calls the two methods above for writing order information to the database. Call the two methods Immediately after the code for writing customer information. The syntax for calling WriteOrder is:

      int orderID = musicOrderRepository.WriteOrder(customerModel.CustId);

      riteOrder(, uses CustID as an input parameter and returns orderId. The second one, WriteOrderItems, takes orderId as an input and returns nothing.
    7. Test! Add a couple of items to the cart and check that they are written correctly to tblOrders and tblOrderItems.

      tblOrders tblOrderItems

    8. With the customer and order information written to the database the only tasks remaining are the order confirmations via the browser and email. The order confirmation will display both customer and order information, which is currently in two separate models. We will create a new model named MusicCheckoutModel that combines the customer and order models. Create this new model as follows:
           public class MusicCheckoutModel
              public int OrderID  { get; set; }
              public DateTime OrderDate { get; set; }
              public CustomerModel CustModel { get; set; }
              public Lis<MusicCartModel> OrderModel { get; set; }
    9. Controller: The new CheckoutModel needs to be populated with data. The Checkout handler already has customer data in customerModel. Assign it to the new model as follows:
           MusicCheckoutModel musicCheckoutModel = new MusicCheckoutModel();
           musicCheckoutModel.OrderID = orderID;
           musicCheckoutModel.OrderDate = DateTime.Now;
           musicCheckoutModel.CustModel = customerModel;
    10. The order information needs to be copied from the database to musicCheckoutModel. Use the method GetAllItemsInCart to retrieve the cart information and then assign it to musicCheckoutModel.OrderModel.
    11. With the order data now written to the database the next step is to empty the shopping cart. Do this by deleting the SessionID. The cart data will still be in tblCart but .NET will generate a new SessionID for the user. Syntax:
         Response.Cookies.Add(new HttpCookie("ASP.NET_SessionId", ""));
    12. View: Display the order information in a view. Create a new View named OrderConfirmation and call it from the controller, passing in musicCheckoutModel. You may use the HTML and CSS from the sample. Visual Studio does recognize the properties of objects inside objects so you will need to display each property, similar to this:


    13. The order date needs to be converted from an object to a string, as follows:
      @Model.OrderDate.ToShortDateString() @Model.OrderDate.ToShortTimeString()
    14. The layout for the items is almost identical to the shopping cart. Cut and paste from the shopping cart. The foreach loop is slightly different:

      @foreach (var item in Model.OrderModel)

    15. Remove the Add/Remove links, add a link to order history, and adjust other formatting appropriately.
  2. Email  (image) - Customers should receive an email confirmation. These instructions describe how to send a plan text email. An optional enhancement is to send an HTML email  (image). HTML emails support images, colors, layout, hyperlinks, etc. but also require complicated styling and layout. Most email clients do not support external stylesheets so all of the sytling information must be included in the email.  Steps:
    1. Create a new folder named Utilities. Add a class named SendEmail.cs and paste in the code from Email class. You do not need to make any changes to this file.
    2. In the utilities folder create a new file named SendOrderConfirmationEmail.cs and add the code from SendOrderConfirmation class. This class takes musicCheckoutModel as an input parameter and constructs a string containing the email confirmation. You will need to make a few modest changes to this file to adapt it to your site.
    3. Controller: In HttpPost Checkout handler add a reference to SendOrderConfirmation just above the return statement. At this stage musicCheckoutModel is populated with both customer and order data. The data is first used for the email and then to display the OrderConfirmation view.
  3. music/history  (image) - This page displays all of a customer's orders. Steps:
    1. Model: Create a new model named MusicHistoryModel. It contains properties for the seven fields in tblOrders and tblOrderItems, excluding CustID.
    2. MusicOrderRepository - Create a method named GetHistory(int custID) that retrieves all of a customers orders and returns List<MusicHistoryModel> . The sql statement joins tblOrders and tblOrderItems. GetRandom() in MusicRepository is a good template.
    3. Controller - Create a handler named History(int id) that calls GetHistory(id) and populates the History view.
    4. View: Create a view named history. You may used the HTML/CSS from the sample site. Otherwise the shopping cart is a good layout template.
  4. About ; - This is a static page that explains a little about the site and lists enhancements. You may cut-and-paste from the sample and modify to your needs.
  5. You are finished with the basic music store. Congratulations!

Submission instructions: Submit assignments via the Canvas course management system. Submit the full URL for each exercise in the assignment, listing the URLs in the same order that they are listed in the assignment. To minimize typos in URLs it is strongly recommended that you copy the URLs from the address bar of the browser rather than trying to type them. Incorrect URLs will not be graded and no credit will be given.

When pages are connected via navigation (as in your music store project) it is only necessary to submit the URL of the first page.

Regular Expressions HTML Color Names Color Picker ASCII Character Codes Unicode Character Codes