DISC 4372 FINAL FALL 2002 Part 1 TO BE COMPLETED IN CLASS (Total of 60 points of 100)

Last Name______________________ Last 4 digits of SS#____________e-mail_________________________________

1. (60 points). The following two tables are contained in the DSN named gm:
je table structure:
sourceref integer NOT NULL,
srnseq integer NOT NULL,
jemajor integer NOT NULL,
jeminor integer NOT NULL,
jesub1 integer,
jesub2 integer,
jedesc char(50),
jeamount numeric (18,2) NOT NULL,
primary key (sourceref,srseq)
entry_control table structure:
srn integer NOT NULL,
mm int, '**** (must be numeric month 1-12)
dd int, '*** (must be numeric day 1-31)
yyyy int, '*** (must be numeric year > 1999)
hh int, '*** (must be numeric hour 0-23)
mm int, '*** (must be numeric minute 0-59)
ss int,'*** (must be numeric second 0-59)
user varchar(50), '*** (name of user who submitted the transaction)
ip_address char(15), '*** (IP address of the user in ddd-ddd-ddd-ddd format)
primary key (srn)
On the first pass an asp program provides a textbox for a user to enter a four digit source source reference number. When this value is returned, the second pass retreives all journal entries for the specified source reference number and the entry _control data. The details are to be returned to the user as an XML file. Use the following XML DTD:

<?xml version="1.0"?>
<!DOCTYPE jv [
<!ELEMENT jv (srn,status,je+, sumdbcr,date,time,user,ip)
<!ELEMENT srn (#PCDATA) >
<!ELEMENT status (#PCDATA) >
<!ELEMENT je (srnseq,jemaj,jemin,jes1,jes2,jedesc,jeamt) >
<!ELEMENT srnseq (#PCDATA) >
<!ELEMENT jemaj (#PCDATA) >
<!ELEMENT jemin (#PCDATA) >
<!ELEMENT jes1 (#PCDATA) >
<!ELEMENT jes2 (#PCDATA) >
<!ELEMENT jedesc (#PCDATA) >
<!ELEMENT jeamt (#PCDATA) >
<!ELEMENT sumdbcr (#PCDATA) > (this is the sum of the jeamount fields for all journal entires in the voucher)
<!ELEMENT date (#PCDATA) > (this is in mm-dd-yyyy format)
<!ELEMENT time (#PCDATA) > (this is in hh:mm:ss format)
<!ELEMENT user (#PCDATA) >
<!ELEMENT ip (#PCDATA) > (this is in ddd-ddd-ddd-ddd format, the first 11 bytes must be 129-184-123)]

The XML DTD in general specifies the contents of the two related tables and requires some reformatting (e.g., date, time) and the calculation of sumdbcr. The status tag will contain the processing results of the query. Specifically, the status tag will contain one or more of the following values:

1. SRN requested is not numeric
2. SRN requested is not exactly four digits
3. SRN requested not found in the je table
4. SRN requested not found in the entry_control table
5. The sum of jeamount fields NOT equal to zero
6. Time field is blank or has an invalid value
7. Date field is blank or has an invalid value
8. User name is blank
9. First eleven digits of user ip address is NOT valid (must be 129-184-123)
10. OK

If any of the first four errors occur, only the jv, srn and status tags are returned (the remaining tags and values are not sent). Note that tests 1 and 2 are edits of the srn data the user sent -- not the values in the tables. Tests 5 thru 9 are performed only if the request passes tests 1 through 4. If any of the error conditions 5 through 9 occur, send the complete XML file as specified in the DTD. The status value "OK" (item 10 above) is sent ONLY if all the request passes ALL the edit tests 1 through 9. Perform NO client side edits. Show the VBScript and HTML for both passes.