DISC 4372 FINAL SPRING 2003 Parks Part 1
Name_______________________________________________SS#____________________________
DSN __________________e-mail address__________________________________________________
1. (60 points) TO BE COMPLETED IN CLASS -- An XML file is sent to the server by a company's customers in order to query the customer_ytd_trans table. This table contains all of the customer's purchases (puchase orders) for the current fiscal year. The table structure is as follows:
cust_id varchar(10)
po_num int
po_status char(3) ("OPN"=in process, "COM"=completed)
po_yr char(2)
po_mon char(2)
po_day char(2)
po_item_num int (begins at 1 and incremented by 1 for each item on the purchase order)
prod_id int
prod_desc varchar(50)
prod_quant int
The primary key is po_num, and po_item_num. Each purchase order has a unique po_num that identifies the purchase order and po_item_num identifies the line number on the purchase order (i.e., each puchase order can have one or more items -- this sequential identifier begins at 1 and is incremented by 1 for each line number). Thus multiple rows may exist in the table for each po_num. A sample request (query) from the customer comes in the form of an XML file structured as follows:
<po_query>
<cust_id>EXXON</cust_id>
<begin_date>
<b_yr>2003</b_yr> (NOTE: b_yr and e_yr will ALWAYS be equal on the query request)
<b_mon>3</b_mon> (NOTE: b_mon will ALWAYS be <= to e_mon on the query request)
<b_day>15</b_day>
</begin_date>
<end_date>
<e_yr>2003</e_yr>
<e_mon>3</e_mon>
<e_day>31</e_day>
</end_date>
</po_query>
The sample above shows EXXON requesting all purchase orders between March 15, 2003 and March 31, 2003 (inclusive). The request comes in the form of a querystring where the XML file (as shown above) is a single string assigned to the variable poq. Note that the query contains two date elements. This date range specifies the time period of the query. The asp program should return all purchase orders for the customer that occurred during this date range. The response is an XML file with the following DTD:
<?xml version="1.0"?>
<!DOCTYPE po_query_response [
<!ELEMENT po_query_response (cust_id, number_found, po_detail*)
<!ELEMENT cust_id (#PCDATA) >
<!ELEMENT number_found (#PCDATA) >
<!ELEMENT po_detail(po_num, po_status, po_yr, po_mon, po_day, po_item_detail+)
<!ELEMENT po_num (#PCDATA) >
<!ELEMENT po_status (#PCDATA) >
<!ELEMENT po_yr (#PCDATA) >
<!ELEMENT po_mon (#PCDATA) >
<!ELEMENT po_day (#PCDATA) >
<!ELEMENT po_item_detail (po_item_num, prod_id, prod_desc, prod_quant) >
<!ELEMENT po_item_num (#PCDATA) >
<!ELEMENT prod_id (#PCDATA) >
<!ELEMENT prod_desc (#PCDATA) >
<!ELEMENT prod_quant (#PCDATA) >
]
Use the following response structure:
response.contentType="text/xml"
xmldata= a string resprenting the XML file po_query_response constructed by the asp program
response.write xmldata
Do not edit the user's request data. Order the response in ascending po_num order. If no purchase orders are found set the tag number_found equal to zero and provide no po_detail. Otherwise number_found is the number of purchase orders found in the table within the date range for the customer and po_detail is provided for each one. Write only the second pass of the asp program (i.e., DO NOT write the client request HTML page).