Assignment 9 - Music Store - Checkout (part 2) & Order History

This week you will finish Checkout.aspx and create OrderHistory.aspx.

1. CheckOut.aspx has three major items left to complete:

1. Insert a new record in tblOrders for each new order
2. Insert a new record in tblOrderItems for each item in the cart.
3. Create an HTML confirmation string for the browser and email.

The "ShipOrder_click." procedure handles all the steps associated with shipping the order. To keep this procuedure to a manageable size it is a good idea to delegate the business logic to classes. The possible procedure names are listed in A08.

Create two new tables: tblOrders and tblOrderItems. OrderID in tblOrders is an autonumber field.

tblOrders
tblOrderItems

The sql to create the tables:

CREATE TABLE tblOrders
(
    [orderID] INT NOT NULL PRIMARY KEY IDENTITY, 
    [orderDate] DATETIME NOT NULL, 
    [custID] INT NOT NULL
)

CREATE TABLE tblOrderItems
(
    [orderID] INT NOT NULL , 
    [asin] VARCHAR(50) NOT NULL, 
    [qty] INT NOT NULL, 
    [title] VARCHAR(200) NOT NULL, 
    [artist] VARCHAR(100) NOT NULL, 
    [price] FLOAT NOT NULL,
    Primary Key (orderID, asin)
)    

Steps for handling order information in ShipOrder_click:

1. Retrieve CustID for new Customers: CustID is needed as an input to tblOrders. For existing customers CustID was retrieved from the database and saved to the ViewState. It can be retrieved from the Viewstate when handling the order.
   
   For new customers the CustID is generated by the autonumber field when a new record is inserted into the database. It needs to be retrieved for writing the order information. SQL Server provides a handy function "SCOPE_IDENTITY()" that returns the value of new identity fields. A single SQL statement can be used to insert new customer data and retrieve the customer ID that is generated by the database. Add the following "scope_identity()" function to the end of the sql INSERT statement:
   

   ; select SCOPE_IDENTITY()

   Modify the method in checkout.cs that you use to add a new customer by replacing the "ExecuteNonQuery" method with "ExecuteScalar." ExecuteScalar is specifically designed for SQL statements that return a single data item. It is almost identical to the ExecuteNonQuery except it returns an integer. Use it to retrieve the CustID of new customers.
   
2. Retrieve Cart Items: Before handing the order information we need to determine if there are any items in the cart.
   1. Use "ListCartItems" from the shopping cart class to fill a dataTable with the items in the cart associated with the user's SessionID. A good name for the dataTable is dtCartItems.
   2. If the cart does not contain any items (dt.Rows.Count == 0) write a brief confirmation message (i.e. "Your information has been updated.") and jump out of the ShipOrder_onClick procedure with a "return;" statement.
      
3. Write Order to database: If the cart contains items then new records need to be inserted into tblOrders and tblOrderItems. Create a new method in checkout.cs named "WriteOrder" to handle this step. Sytnax:
   public static int WriteOrder(DataTable dtCartItems, int CustID) .
   
   1. Write to tblOrder: The WriteOrder(...) method use a sql INSERT statement to add a new record. Use the "scope_identity()" function in the insert sql statement with ExecuteScalar to retrieve the OrderID, as done with the custID.
      
   2. Write to tblOrderItems: Each item in the cart needs a separate record in tblOrderItems. A single SQL statement can be used to copy the item information from tblCart to tblOrderItems. The sql statement uses a Select statement nested inside of an Insert Into statement (See "sql insert subquery"). The select statement retrieves the relevant records from tblCart and the Insert statement inserts them into tblOrderItems. The following statement shows the syntax and uses two parameters: @orderID and @sessionID.

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

   3. Remove the items from tblCart using any one of the following three methods:
      1. Delete the items in tblCart associated with the current SessionID.
      2. Tag the items in tblCart with an OrderID so that we know they have been shipped.
      3. Abandon the current Session using Session.Abandon(). To force a new SessionID you will also need to delete the current sessionID from the cookie using:

         Response.Cookies.Add(new HttpCookie("ASP.NET_SessionId", ""));

4. Create HTML Confirmation String: With the order written to the database you are now ready to prepare a confirmation message for the user. From ShipOrder_onClick call a function named WriteConfirmationString(intOrderID, dtCartItems). This function returns a string of HTML containing customer and order information.
   
   Creating the HTML for the confirmation is tedious, but we can leverage our work by using the same HTML for both the browser confirmation and the email confirmation. The string should contain the order number, shipping address and order item information.
   
5. Send Email: Use this SendEmail class (source) to send an HTML email. Unfortunately many email clients do not support styles sheets (check current status on campaignMonitor.com) so styles must be inline. The syntax to use the class is something like this:

   String strConfirm =
   "<body style='background-color:#F5F5F5'>" +
   "<div style='text-align:center'>" +
   "<h2 style='color:#CC0000'>Order Confirmation from XML Music</h2>" +
   
   "<table style='padding:3px;'><tr> " +
   "<td>Order Number:</td>" +
   "<td>" + OrderID + "</td></tr>" +
   "<tr><td style='vertical-align:top;'>Shipped to:</td><td>" +
   tbFName.Text + " " + tbLName.Text + "<br />" +
   tbStreet.Text + "<br />" +
   tbCity.Text + " " + tbState.Text + " " + tbZip.Text +
   "<br /><br /></td></tr>" +
   "<tr><td style='vertical-align:top;'>Titles:</td>" +
   "<td>" +
   "<table style='padding:3px;'>";

   //Loop through items in cart and add info to strConfirm
   foreach (DataRow dr in dtCart.Rows) )
   {
      double price = Convert.ToDouble(dr["Price"]);
      price = Math.Round(price, 2);
      int quantity = Convert.ToInt32(dr["Qty"]);
   
      strConfirm += "<tr>" + "<td><span style='color:#006699;'>" +
      dr["Title"] + "</span><br />" +
   ....

   //Add shipping cost and total
   strConfirm += "</table></td><tr>" +
   "<tr><td>Sub-total:</td><td>" + Subtotal + "</td></tr>" +
   ...
   
   //Add links and images
   strConfirm += "<br />" +
   "<a href='http://yorktown.cbe.wwu.edu/sandvig/MIS324/assignments/musicstore/'><img border='0' " +; +
   "src='http://yorktown.cbe.wwu.edu/sandvig/mis324/assignments/musicstore/MusicImages/continue-shopping.gif'></a></p>";
   
   //Send email
   string subject = "Order Confirmation from XML Music";t;;
   
   //Email class returns a text string with status.
   
   return SendEmail.Send(toEmail, toName, "csandvig@wwu.edu", "XML Music", subject, strConfirm);

   Where toEmail is the destination address, toName is the recipient's name, and msgBody is an HTML string containing the shipping and product information. Anchor tags and image src's must include the full server URL, as shown in the code above.ve.
   

2..2. OrderHistory.aspx

This page is similar to SearchBrowse but with a different SQL statement. The SQL statement uses CustID to retrieve the customer's history.

There are a couple ways of getting the CustID to OrderHistory.aspx.

1. CustID as parameter in the querystring. It is easy to add a parameter the OrderHistory.aspx link that contains the CustID but we don't want curious and malicious users to change the Customer ID in the querystring and see order histories of other users. Disguise the ID by multiplying it by a large integer (5-6 digits) when writing the anchor tag on the checkout page and dividing it by the same large number in the OrderHistory page. The large number acts as a simple encryption key.
2. CustID in a session object. This has the advantage that the CustID does not need to be encrypted, but the disadvantage that it will timeout after 20 minutes and cannot be used in emails.
   

Congratulations, you have completed your ASP.NET Music Store!