N342 Home Work 5 - Project Databases/XML/XSL

Modified:

Download Home Work 5 files

Note

IIS Web server sessions expire after about 20 minutes when no file in the Project directory is accessed, abandoning all session variables (i.e. ASP set cookies) including Session("trader"), effectively logging the trader out. You may need to login or execute SessionTrader.asp after an extended period of not accessing the Web Server.

IE caches files by default which can prevent opening of a later version. The effect is particularly noticeable during repeated tests with an ASP file that has been changed but is not opened because of a cached version.
The solution is to require IE to check for new versions on every visit to a page. In IE:

Tools | Internet Options | Temporary Internet Files | Settings | Every Visit to Page

When running your ASP, receive error message about "Site may be down, try again later. Etc." IE, by default, does not display errors from a Web server.
Turn OFF friendly error messages by, in IE:

Tools | Internet Options | Advanced | Browsing | Uncheck "Show friendly HTTP error messages

Access denied - Blocked

The following error may occur when accessing XML files downloaded from one computer to another such as the ZIP files for the assignment. Vista (and perhaps XP) blocks access to files, presumably attempting to protect you from using files you have copied to your computer.
Unblock access to individual files by the following:

Open in Windows Explorer the directory containing the file, either the XML file or one referenced by the XML.

Right click on the file and open Properties.

Click Unblock.

The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.
Access is denied. Error processing resource 'file:///W:/N341/Project/TraderSummaryContent.htm'.

Unblock access to all files in a directory by the following:

Download Streams, copy Streams.exe to Windows directory if possible.

To unblock all the files in directory c:\temp and any subdirectories enter:
Streams –d –s c:\temp

Overview

Three key stock trading functions remain:

removing cart contents,
committing selected stocks to buy or sell,
and producing a summary of stock trading performance.

Home Work 4 completed logic for adding stocks to buy (BuyStock.asp) or sell (SellStock.asp) to the shopping cart, a metaphor for the Cart database table.

Logic for viewing the cart (CartSummary.asp) was also implemented.

1. Cart removal (CartEdit.asp)

Remove unwanted trades from the cart as marked by a trader.

When completed redirects to CartSummary.asp to display the shopping cart contents, no need to write a style sheet.

The stocks to remove are input on the URL address line from the browser, designated by the unique TRADEID number. To designate removal of TRADEID 95 and 101, the address line would be:

http://iu-uits-eiwp1.ads.iu.edu/username/N342/Project/CartEdit.asp?95=on&101=on

Determining trades to remove is similar to BuyStock.asp and SellStock.asp.

The TRADEIDs can be determined with the Request.QueryString object:

Request.QueryString.Count is the number of <key,item> pairs on the URL line.

Request.QueryString.Key(i) is the ith key (e.g. Key[1] is '101').

Request.QueryString.Item(i) is the ith item value (e.g. Item[1] is 'on').

Testing

Add several trades to the Cart table for a trader.

Login or execute Sessiontrader.asp to assign variable Session("trader") to the trader ID.

For the Cart table at right, change the URL to:

http://iu-uits-eiwp1.ads.iu.edu/username/N342/Project/CartEdit.asp?19=on

After execution, the Cart table should show the record deleted.

Verify that when completed, redirects to CartSummary.asp to display the remainder of shopping cart contents, minus those deleted.

2. Committing (CartCommit.asp)

The purpose of this assignment is to implement logic that commits the stock trades contained in the Cart for the current logged in trader. That is, stocks are bought or sold against the trader's portfolio of stocks and account balance.

The logic for committing the trades can be stated as follows:

First - Verify that the Session("trader") variable is defined, redirect to Login.xml if not defined.
Recall that you can edit SessionTrader.asp to initialize the variable to a specific trader, to avoid logging in.

Before Selling - Verify that the shares of a stock owned are at least equal to the shares of that stock in the Cart to be sold.
Make the simplifying assumption that this must be true for each individual sale, even though a trader can buy and sell the same stock within the same Cart.

Note the assumption means the trader is not selling the same stock more than once for each commit of the cart.

The necessary records are produced by a SELECT using an inner join of the Cart and Portfolio tables on SYMBOL and ID selecting records where ID is the trader and BUY is false. The INNER JOIN includes the following:

FROM CART INNER JOIN PORTFOLIO ON CART.SYMBOL = PORTFOLIO.SYMBOL AND CART.ID = PORTFOLIO.ID WHERE ...

This is sufficient because the SellList.asp module listed stocks to sell from the trader's portfolio, there is no stock to sell in the cart that is not in the portfolio.

Iterate through the record set produced by the SELECT verifying a sufficient number of each stock. Redirect to page "userStockShortage.xml" when any stock owned is less than the shares of that stock to sell. Do not alter any database tables if there are insufficient stocks owned.

Note that access to fields in the recordset can be simplified by naming fields. For example, naming CART.SHARES field as SELL:

rs=conn.Execute("SELECT CART.SHARES AS SELL, PORTFOLIO.SHARES AS OWN

allows recordset operations to be written as:

if( rs("SELL") > rs("OWN") ) ...

Testing

Add records to Portfolio table for one or two stocks.

Add records to Cart table for the same stocks marked to sell (i.e. BUY is unchecked).

In the ASP, add a Response.Write statement to display the number of stocks owned and to sell.

Execute Sessiontrader.asp to assign variable Session("trader") to the trader ID.

Enter the URL:

http://iu-uits-eiwp1.ads.iu.edu/username/N342/Project/CartCommit.asp

Verify that a stock shortage is detected and redirects to "userStockShortage.xml".

Before Buying - Verify a sufficient balance for the total stock purchase after stocks in the cart are sold above.

Determine the current balance from the Trader table.

Compute the new balance from the selling of stocks in the Cart table.
Recall that we verified above there are sufficient stocks owned to sell.

Include the 5% of the stock value commission charged by our trading company for each sale.

For example, if 5 shares of a stock valued at $20.00 per share were sold, the commission on the $100.00 sale would be $5.00, and $95.00 would be added to the balance.

This step can be incorporated into the selling of stocks above.

Compute the new balance after buying stocks in the Cart.

Include the 5% of the stock value commission charged by our trading company for each purchase.

For example, if 10 shares of a stock valued at $10.00 per share were purchased, the commission on the $100.00 would be $5.00, and $105.00 would be the total purchase cost subtracted from the balance.

Redirect to page "OverDraft.xml" when insufficient funds are available, that is, the balance becomes negative. Do not alter any database tables if there are insufficient funds.

Testing

Add a balance to the Trader table for your trader.

Add records to Portfolio table for one or two stocks for that trader.

Add and mark to sell records to Cart table for stocks in Portfolio table and different stocks marked to buy.

In the ASP, for testing, add Response.Write statements to display:

initial balance

value of stocks in Cart table to be sold

balance after the stock in the Cart table are sold (from above)

balance after the stock in the Cart table are bought

Verify that insufficient funds is detected and redirects to "OverDraft.xml".

Login or execute SessionTrader.asp and enter the URL:

http://iu-uits-eiwp1.ads.iu.edu/username/N342/Project/CartCommit.asp

Committing a buy.
We have verified above that there are sufficient funds from the balance in the Trader table and selling stocks.

There are now two cases to handle for Portfolio table in committing a buy:

Stocks not in the Portfolio table must be inserted from the Cart into the trader's Portfolio.

Stocks already in the Portfolio must be updated by adding the number of shares in the Cart.

The trade must be inserted into the permanent record of trades, the Trades database table.

Examine the Trades table for the necessary fields.

Note that TRADEID and TIMEDATE are automatically added when any new record is inserted (i.e. INSERT INTO TRADES (ID, SYMBOL, PRICE, SHARES, BUY) VALUES ...) does not include TRADEID or TIMEDATE.

Note that multiple recordsets can be defined simultaneously. One can then write statements such as:

var rc = conn.Execute("SELECT * FROM CART WHERE ID = '" + trader + "';");

while( !rc.EOF ) {
    var rp = conn.Execute("SELECT * FROM PORTFOLIO WHERE SYMBOL = '" + rc("SYMBOL") + "' AND ID='" + trader + "'");
    rc.MoveNext();
}

The above example:

first selects all records for a trader from the CART table (rc recordset) and,

using the current CART record, selects the PORTFOLIO record with the matching rs("SYMBOL") for that trader.

Testing - The tables at right will buy 5 shares of UAL for trader Ray.

Add a balance to the Trader table for your trader.

Add records to Portfolio table for one or two stocks.

Add and mark to buy records to Cart table for new and existing stocks in Portfolio table.

In the CartCommit.ASP, for testing, add Response.Write statements to display:

initial balance

value of stocks in Cart table to be sold

balance after the stock in the Cart table are sold (from above)

balance after the stock in the Cart table are bought

After execution:

check the Trades table that the trade was added correctly,

and that the Portfolio table was correctly updated or new record inserted.

Enter the URL:

http://iu-uits-eiwp1.ads.iu.edu/username/N342/Project/CartCommit.asp

Committing a sell
We have verified above that there are sufficient stocks in the Portfolio table to sell.
Modifying Portfolio table has two cases after a stock is sold:

Update a stock in Portfolio when the stock has more than zero shares.

Delete a stock from Portfolio when the stock has zero shares.

Each sell trade is inserted into the permanent record of trades, the Trades database table with the BUY field false.

Note that TRADEID and TIMEDATE are automatic fields and are automatically inserted (i.e. INSERT INTO TRADES (ID, SYMBOL, PRICE, SHARES, BUY) VALUES ...

Testing - The tables at right will sell 10 shares of MSFT for trader Ray. Portfolio table reflects the buy and sell results (see initial Portfolio table for part 2 above).

Add a balance to the Trader table for your trader.

Add records to Portfolio table for one or two stocks.

Add and mark to sell records to Cart table for stocks in Portfolio table and different stocks marked to buy.

In the CartCommit.ASP, for testing, add Response.Write statements to display:

initial balance

value of stocks in Cart table to be sold

balance after the stock in the Cart table are sold (from above)

balance after the stock in the Cart table are bought

After execution of buy and sell:

check the Trades table that the trade was inserted,

and that the Portfolio table was correctly updated and any 0 shares records deleted.

Enter the URL:

http://iu-uits-eiwp1.ads.iu.edu/username/N342/Project/CartCommit.asp

Account balance - The balance is updated in the Trader table to reflect the stocks bought and sold less the 5% commission on the value of each stock trade.
Testing - For trader Ray, the tables:

sold 10 shares of MSFT at $20.00/share or $200.00

bought 5 shares of UAL at $34.00/share or $170.00

$200.00 - $170.00 = $30.00 was earned.

paid a 5% commission on $370.00 of $18.50

the balance in the Trader table increased by:
$30.00 - $18.50 = $11.50

from $10,043.41 to $10,054.91

Enter the URL:

http://iu-uits-eiwp1.ads.iu.edu/username/N342/Project/CartCommit.asp

Cart - After all trades have been committed the Cart table records are deleted for the trader.

CartCommit Completed - Redirect to page "Portfolio.asp" showing the trader the portfolio.

3. Performance (TradingPerformance.asp)

The trading performance summary consists of a balance for all stock trades and the total of commissions paid.

The balance is the current difference between buying and selling stocks.

If 1 share was bought at $1.00, the balance would be -$1.05, the stock cost plus 5% commission.

The logic is:

Start with a BALANCE=0.

Select all records from the TRADES database table for the logged trader.

For each selected record:

Compute the value of each trade as PRICE*SHARES; add to the BALANCE if sell or subtract from the BALANCE if buy.

Sum the COMMISSION as 5% of the value of each trade.

Subtract the COMMISSION from the BALANCE.

Place the BALANCE and COMMISSION in an XML tree.

Display BALANCE and COMMISSION by transforming the XML with XSL.

The key difference from other project modules is that the XML data for BALANCE and COMMISSION is not generated from a database but is computed above.

However, BALANCE and COMMISSION data values can be placed in the XML tree.

The XML tree can be defined as text and created by:

var xmlData = Server.CreateObject("Microsoft.XMLDOM");
xmlData.async = false;
xmlData.loadXML("<PERFORMANCE><BALANCE/><COMMISSION/></PERFORMANCE>");
The elements <BALANCE> and <COMMISSION> are to be assigned a text value corresponding to the computed BALANCE and COMMISSION.
A value of 123.45 is assigned as the text of the BALANCE node by:
var root = xmlData.documentElement;
var node = root.selectSingleNode("BALANCE");
node.text = 123.45;
The XSL transformation file is "TradingPerformanceContent.htm", downloaded for HW5. 
The XML/XSL transformation can then performed as usual by:
var xslDoc = Server.CreateObject("Microsoft.XMLDOM");
xslDoc.async = false;
xslDoc.load(Server.MapPath("TradingPerformance.htm"));
Response.Write(xmlData.transformNode(xslDoc));
For more on tree manipulations, see class notes.
Testing requires placing several buy/sell stock transactions in the TRADES table for a registered and logging in the trader.

Enter the URL:

http://iu-uits-eiwp1.ads.iu.edu/username/N342/Project/TradingPerformance.asp

4. Global.asa

The file, Global.Asa, is part of HW5 files and should be copied into the Project directory. The Global.Asa file should automatically be executed when a session starts and ends; the purpose is usually to perform session initializations on start, logout traders and delete the cart when the session times out.

However, at the current time, the IUS server is not properly executing the Global.Asa file. Please ignore for this assignment but it would need to be resolved for a production system. The default sessions timeout limit and the one used at IUS is set at 20 minutes.

On opening an ASP script on a virtual directory for the first time, the server first reads the file Global.Asa from the root directory of the same virtual directory of the script. The Global.Asa file would normally contain session timeout limits, initialization, etc. The example below contains sets the important Session.Timeout to 1 minute. If the client associated with the session has no activity in 1 minute that causes a call to the Session_OnEnd subroutine in the Global.Asa script by the server, the server undefines the session variables and effectively forgets about the client. The Session.Timeout default is 20 minutes.

For the project, use the following Global.asa (copied with the Home Work 5 files). Its purpose is, on an inactivity timeout, to remove any stocks from the cart for the trader and to logout the trader.

If timeouts occur too soon, change the Session.Timeout = 1 to a larger value.

<Script LANGUAGE = VBScript RUNAT = Server> 

Sub Session_OnStart 
  Response.Expires = 1 ' Page cached 1 minute
  Session.Timeout = 1 ' End session after 1 minutes 
 End Sub 

Sub Session_OnEnd
  if(Session("trader")<>"") then
    set conn = Server.CreateObject("ADODB.Connection") 
    conn.Mode = 3
    conn.Open ("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("Project.mdb")  )
    conn.Execute("UPDATE TRADER SET LOGGED = 0 WHERE ID='"&Session("trader")&"'")
    conn.Execute("DELETE FROM CART WHERE ID='"&Session("trader")&"'")
    conn.Close()
  end if
End Sub 
</Script>

Assignment

Complete:

Remove unwanted trades from cart (CartEdit.asp).
Commit stock trades from cart (CartCommit.asp).
Calculate the performance as the balance and commission of trades (TradingPerformance.asp)

Turn in - Due before class on the date listed in the syllabus.

OnCourse Drop Box
1. Compress the Project folder containing the homework files.
  - In Windows Explorer, right-click on the Project folder.
  - Click Send to then click Compressed (zipped) Folder.
  - Project.zip will be created.
2. Upload Project.zip to your CSCI N342 OnCourse Drop Box using the Display Name of HW5.
Web server
1. Copy to W: drive.
2. Test in IE.