DISC 4372 - Fall 2002 - Parks-Name_______________________ SSN___________________

An accounts receivable system maintains customer account information and current balances. The customer account table is called cust and the monthly transaction file is called trans. The tables have the following CREATE structures:

CREATE TABLE cust (
acct-num int NOT NULL,
pin-number int NOT NULL,
(user's secret acccess key)
lastname varchar(30) NOT NULL,
firstname varchar(30) NOT NULL,
middlename varchar(30),
address-1 varchar(30) NOT NULL,
address-2 varchar(30),
city varchar(30) NOT NULL,
state char(2),
zip char(5),
phone char(10),
balance decimal (12,2),
last-payment decimal(12,2),
last-payment-date varchar(20),
primary key (acct-num))

CREATE TABLE trans (
acct-num int NOT NULL,
trans-seq int NOT NULL,
   (a sequence number 1,2,3...
    begins at 1 each month)
month char(2) NOT NULL,
year char(2) NOT NULL,
type char(1),
   ("P" for payments and "T" for purchases)
vendor varchar(30),
   (used only for type="T" otherwise blank)
date varchar(30),
trans-amount decimal (12,2),
   (positive for purchases (type="T"),
   negative for payments (type="P")
primary key (acct-num,month,year,trans-seq))

1. (50 points) On the first pass, the asp program provides the user with four textboxes: (1) acct-num; (2) pin-number; (3) month; (4) year, and a submit button. Assume five data fields are returned and are named: acct; pin; mon; yr; and token. On the second pass, the program produces a report of desired month/year activity from the trans table. It shows (in order):

Account number, month, year, first, last and middle names at the top of the page (format of your choice)
A table showing monthly activity for the month and year supplied by the user. The table contains one row for each transaction in the trans table for the chosen month/year. The data in the table appears in the following order:
1. all purchases (type="T") for the desired month/year in ascending date order (show vendor, date and trans-amount)
2. a total for the monthly purchases
3. all payments (type="P") for the desired month/year in ascending date order (show date and trans-amount)
4. a total of the monthly payments
5. a value for "NET ACCOUNT BALANCE CHANGE (i.e., total purchases - total payments)

Assume the account number exists and the pin number is correct. Show ONLY the VBScript for the second pass (i.e., NO HTML or code for the first pass, NO code for main). Assume the data has been edited for type and size on the first pass.
2. (50 points) Write the entire asp program to allow the user to change their pin number. On the first pass provide:

a texbox for their current account number (name this box ca; adjacent text says: "Account Number"; size is 4)
a password box for their current pin number (name this box cp; adjacent text says: "Current PIN number"; size is 4)
a password box for their new pin number (name this box np1; adjacent text says: "Enter a new PIN number here"; size is 4)
a password box to confirm their new pin number (i.e., they enter their new pin number twice) (name this box np2; adjacent text says: "Re-enter the PIN number to confirm your entry:"; size is 4)
a submit button

On the client side when the user clicks the SUBMIT button, compare the value of the first new pin number entry with the second new pin number entry. If they DO NOT match: (1) Clear the second and third password boxes; and (2) display the message "New PINs DO NOT MATCH -- Try again". Also test to assure that the new pin(s) DO NOT equal the current pin (i.e.., they must enter a new pin number). If the new pin(s) equal to the current pin: (1) Clear the second and third password boxes; and (2) display the message "New PIN cannot be the same as the Current PIN -- Try again". If the new pin entries match AND are not equal to the current pin, the program submits the form. When recevied on the server side, the asp program checks the current pin number for the account. If the pin number (i.e., the value of cp that was sent) doesn't match the value in the table, send a message to the user that says: "The current PIN number you entered is invalid for your account -- try again" and provide the same fields as you did on the first pass (NOTE: all fields should be emtpy). If the current pin number the user sent is valid, modify the cust table to reflect the change. If successful send a message to the user that says: "PIN Number changed OK" otherwise send an error message.