CSG2431 ECU Room Booking System Phase 1

Assignment Overview and Requirements

In this assignment, you will build skills in both client and server-side web development, particularly in the essential areas of form processing, validation, database interaction and sessions. You are required to build a MySQL database driven HTML and PHP website that allows ECU staff to book meeting rooms on ECU campuses. Staff members can register an account on the website and log in to make/cancel room bookings, and administrators can also manage the rooms that are available.

Public Functionality

People who visit the website without being logged in can only access the following:

• A registration page where staff can register an account.
• A login form containing fields for a username and a password, and a submit button.

You are welcome to design the look and layout of the website however you like, as long as the functionality is implemented. There are example images throughout this brief, but you are not required to create a website that resembles the examples or looks pretty.

Figure: see image.

Before a staff member can log in, they must fill out the registration form to create an account. The registration form must include the following fields:

• Username.
• First name and surname (as two separate fields).
• Telephone extension number.
• Password (and a password confirmation field).

The form should be validated using both JavaScript and PHP. All fields are required, the username must be at least 5 characters long, the telephone extension number must be a four-digit number (e.g. "6283") and the password must be at least 5 characters long*. Your PHP code must check that the username does not already exist in the database.

When the form is submitted and validated successfully, the details should be inserted as a row in the "user" table of the database. This table should also contain a column named access_level, which should have a default value of staff. The staff member can then log in to the website via the login form using their username and password.

* A minimum password length of 5 characters is not very good for security, but will make development, testing and marking easier since less time is spent typing in passwords.

Staff Functionality

The staff section of the website allows staff to find the desired meeting room, make bookings, see their upcoming bookings, and cancel bookings. Details of bookings are stored in the "booking" table of the database and details of meeting rooms are stored in the room table of the database.

The area that staff members are taken to after logging in must:

• Display their name, e.g. "Welcome, Joe Bloggs" or You are logged in as Joe Bloggs.
• Contain a link to log out.
• Allow staff to search for a room (a form containing a text field and a submit button).
• Allow staff to list all rooms.
• Display a list of the staff member's upcoming room bookings.
  • Only bookings that have not yet ended should appear in the list.
  • Order the list by the start time of the bookings.
  • Beside each booking there should be a link for the staff to cancel the booking.

Clicking the "Cancel" link next to an upcoming booking should delete the appropriate row in the booking table in the database. Include a confirmation prompt when a Cancel link is clicked to make sure that bookings are not accidentally deleted.

Figure: see image.

Logged in staff members can either search for a room number or view the list of all rooms to find the room they want to book. The list of rooms and results of a search only needs to include the room number and a link to "View/Book" the room, and should be ordered alphabetically.

Remember, it is up to you to decide how to lay out the website and whether to do/show multiple things on a single pay or split things up onto separate pages. Try to make the site pleasant for users to use!

Clicking a "View/Book" link will take the staff member to a page containing the room details (capacity), upcoming bookings and a booking form where they specify the start time and end time of the booking. You are welcome to use HTML5 input types such as date, time or datetime-local (pictured) for the fields of the booking form.

Figure: see image.

Once the staff member completes and submits the booking form, use PHP to check the following:

• The start time and end time are both valid (e.g. a date of February 30th is invalid).
• The start time is later than the current time.
• The end time is later than the start time.
• The new booking does not overlap any other existing bookings for that room.

If the booking cannot be made because it does not meet any of the criteria above, display an error message, e.g. "End time must be later than start time" or This booking overlaps existing bookings.

If the booking meets all the criteria above, details of the booking should be inserted into the "booking" table in the database. This will need to include the start time, end time and foreign key values to identify the room and staff member.

Some PHP functions that may be useful when validating the start time and end time are:

• date_parse(), which can be given a string of a date and time and will return an array full of the individual components - day, month, year, etc. The array also mentions if the date was invalid.
• strtotime(), which can be given a string of a date and time and will return its "Unix timestamp", which represents the exact date and time as a number. Unix timestamps can easily be compared to determine if one date is later than another.
• time(), which returns the current date and time as a Unix timestamp.

Admin Functionality

Some staff members are designated as admins. As well as the staff functionality described above, admin users can manage the rooms in the system and cancel any booking. Admin users are also stored in the "user" table of the database, with an access level of admin. You do not need a registration form for admins - simply add some users with an access level of admin directly into the database.

Admins users log in to the website in the same way as staff and can access all the staff pages and functionality described above, with the addition of the following admin-specific abilities:

• The ability to view and cancel any upcoming booking (not just their own).
  • When showing upcoming bookings for a meeting room, include a "Cancel" link next to each booking.
• The ability to manage rooms. As well as being able to search, list and view rooms via the existing staff functionality, this involves being able to:
  • Add new rooms to the database.
  • Edit the capacity of rooms in the database.
  • Delete rooms from the database.

To add a new meeting room, the admin staff member must submit a form consisting of a room number (e.g. "JO19.109") and a capacity. Ensure that the fields cannot be left blank, the capacity is an integer above 0, and the room number does not already exist in the room table. If all the conditions are met, insert the details of the new room into the room table.

To edit an existing meeting room, include "Edit" links alongside the View/Book links whenever an admin staff member is looking at search results or the room list, and when they are viewing a room. Clicking this link takes them to a form where they can change the capacity (must remain above 0).

To delete an existing meeting room, include "Delete" links alongside the View/Book and Edit links whenever an admin staff member is looking at search results or the room list, and when they are viewing a room. Clicking this link should delete the room from the room table, as well as all bookings for that room from the booking table. Include a confirmation prompt to prevent accidental deletion.

Figure: see image.

Session-Based Access Control

You must use sessions to ensure that the staff-only and admin-only functionality can only be accessed/performed by users logged in with the appropriate access level. When a user logs in, store their access level and username into session variables called "access_level" and username. You may also want to create a session variable containing the user's name, to conveniently display it.

Check that the "access_level" session variable exists and/or contains an appropriate value whenever you need to determine whether to show or allow something. For example, check that the variable exists when dealing with things that all staff can do, and check that it exists and contains admin when dealing with things that only admins can do.

It is not enough to just prevent a form or link from appearing based upon session variables. You also need to check the session variables in the code that implements the functionality (in the pages that forms/links end up requesting) to ensure that access is properly controlled.

If you use the "access_level" session variable well, the same files/pages can be used for all access levels. For example, you will only need a single view room page for all access levels, rather than one for normal staff, and one for admin staff.

The "username" session variable should be used in SQL queries when a user creates a booking, and when normal staff members delete a booking (to ensure that they can only delete their own bookings).

When the user logs out, destroy the session variables and session.

You can complete almost the entirety of the assignment without sessions prior to this point:

• In lieu of the "access_level" session variable, simply assume that all users are admins. i.e. Implement all of the functionality that a page would offer to an admin user and do not restrict access to anything. Then, add code to control/restrict access once you have covered sessions.
• In lieu of the "username" session variable, simply create an equivalent variable at the top of any page that would need it - e.g. $username = 'jbloggs'; (make it match a username in the user table). Use this variable in any code and SQL queries as needed, and then replace it with the username session variable once you have covered sessions.

Website Functionality Summary

The website offers different functionality depending on whether the user is public, normal staff, or admin staff. Ensure that users can conveniently navigate between pages as necessary/appropriate. This is a summary of the functionality available to each type of user: see image.

Remember that you can design and lay out your website however you like, as long as the functionality is implemented. Focus on implementing functionality before spending time on design or aesthetics!

Database Structure

The database storing the data must be well-structured and efficient. A suggested structure is presented below.

Figure: see image.

The following sample data should help you to visualise the database (primary keys are in grey):

user table:

booking table:

room table:

You are welcome to create auto-incrementing ID columns to serve as the primary key for the "user" and room tables rather than using the username and room number if preferred.

Based on this data, you can see that Joe Bloggs has booked JO18.204 (which has a capacity of 25) from 10:30 until 12:30 on July 2nd, and that Barry Howard has booked ML14.115 (capacity of 14) from 16:00 on July 12th until 08:30 on July 13th, and so on.

Ensure that all fields in your database tables use the most appropriate data type to efficiently store the data they require. Use the "DATETIME" data type for the start time and end time of bookings.