MIS 4372 MIDTERM PARKS SPRING 2011

Last Name _________________________________________________________ Last 4 digits PSID __________

1. (50 points) Fast Gene's Systems Development Company designs, develops, and implements software solutions. Employees are required to record their "daily billable hours" for the company's clients. The employees record their "daily billable hours" in a table called bhours. Another table called clients is also available. The table structures are:

bhours clients
trans_id bigint identity (1,1),
employee_id int,
client varchar(30),
task varchar(15),
date_year int,
date_month int,
date_day int,
billable_hours numeric (5,1)
PRIMARY KEY (trans_id) 		client_id bigint identity (1,1),
client_name varchar(30),
PRIMARY KEY (client_id)
Create a two pass *.asp program named billable.asp to record (i.e., insert) an employee's work efforts in the bhours table. Assume the bhours and clients tables already exists as shown above. The program should:
  1. In Pass 1, create a form that allows the employee to record their daily billable hours by client, task and date. The form should provide:
    1. a textbox for the employee ID (6 digits)
    2. a select box with the names of ALL the clients as options in alphabetic order (Note: this will require rolling through the clients table to produce the option tags)
    3. a textbox for 4 character year. Preload this textbox with the value of the current year. Use the following to obtain the current year string:    cstr(Year(Date))
    4. a select box with the twelve month options (1 → 12)
    5. a select box with the 31 day options (1 → 31)
    6. a select box with the following task options:
      1. Client Meeting
      2. Design
      3. Programming
      4. Testing
      5. Installation
      6. Documentation
      7. Modifications
    7. a textbox for the number of hours worked on the task (five characters max)
    8. a hidden value for the pass token
    9. a submit button

  2. In Pass 2, insert the data from Pass 1 into the "bhours" table. Respond with "Failed" if the task fails else respond "Inserted OK".

Show the code for (a) main routine; (b) Pass 1; and (c) Pass2. Perform NO data edits in Pass 1.

2. (50 points) Write a report using the bhours table that shows ALL transactions. Show the following columns in the output table:

  1. client
  2. employee_id
  3. task
  4. date (year, month, day) in this one column
  5. billable hours

Order the report by:

  1. client in alphabetical order
  2. employee id in ascending numeric order
  3. task in alphabetical order
  4. date (year, month, day) -- oldest first

Provide at the end of the report: (a) a count of the transactions; and (b) a sum for ALL billable hours.