CSG2431 ECU Room Booking System Phase 2

Assignment Overview and Requirements

In this project, you build upon room booking website you developed in the first assignment. You will be adding some more features and functionality to the website. Some the work you will do in this assignment will involve modifying your files from the assignment, while other aspects will involve creating new files. The database will also need to be modified or expanded in some places.

Password Hashing

User passwords can no longer be stored in plaintext in the database - instead, a hash of the password should be stored. This will involve modifying the code that processes the registration form and the code that processes the login form.

You will need to change the passwords of existing users in the database to appropriate hashes so that they can log in once you have implemented the above feature. To do this, generate a hash of their plaintext password using code from Example #1 of the password_hash() documentation and replace their plaintext password in the database with the resulting password hash.

Room Equipment & Notes

To make the system more useful, the database must now store details about the equipment available in each room. Whether or not a room contains the following equipment must be stored:

• Whiteboard
• Laptop Connection
• Teleconferencing System

Keeping track of these details simply requires three "true/false" (boolean) values in the room table - use a data type that you feel is appropriate. Also add a notes column to the table, which will be used to contain any other relevant information about the room. Use a data type/length suitable for storing approximately a paragraph of text.

Admin users must be able to specify which pieces of equipment a room contains (using checkboxes) and write a note (optional) when they add a new room or edit an existing room in the database. When editing a room, make sure that the fields of the form are filled in/checked as per the existing data.

Figure: see image.

To make this information useful, all users need to be able to view it. Whenever the details of a room are viewed by staff or admins, equipment details and notes should be shown.

Figure: see image.

All users should also have access to an "equipment search" form. The form should include a checkbox for each different piece of equipment, allowing the user to find rooms containing those pieces of equipment.

Figure: see image.

Display search results in the same way as the first assignment, i.e. the room name followed by a "View/Book" link (plus Edit and Delete links for admin users).

Event Logging

Whenever a significant event happens on the website, a log of it should be recorded in the database. Logs will be inserted into a "log" table in the database, which needs the following columns:

• "log_id" - an auto incrementing integer (primary key)
• "log_date" - a TIMESTAMP column which has a default value of the current date and time
• "ip_address" - the IP address of the user who performed the action
• "event_type" - a short description of the event that occurred, e.g. Room Deleted
• "event_details" - a more detailed description of the event, e.g. JO18.142 deleted by bwoods

The following events must be logged:

It will involve adding code to INSERT data into the "log" table at the points where these events occur in your code. Since the process is mostly the same regardless of event type, this can be streamlined by defining a function.

A page to view the logs in the "log" table should be created, only accessible to admins. All details of logs should be shown (except the log ID), and they should be ordered by date (most recent at the top).

Figure: see image.

Site Statistics

Admin users should have access to a page that displays statistics drawn from the data in the database. These statistics should be determined via SQL queries whenever the page is accessed - you do not need to change the database or store any additional data regarding the statistics.

The page should display the following statistics:

• The total number of bookings, rooms, staff users and admin users (as 4 separate totals).
• The average number of bookings per room, rounded to the nearest whole number.
• The name of the user who has made the most bookings (and their number of bookings).

Figure: see image.

These statistics will involve writing SQL queries that include aggregate functions and the GROUP BY clause. Aim to write efficient queries and minimise the number of queries needed overall.

Database Change Summary

Here is a summary of the database changes needed to implement the new features:

• The "user" table will need to ensure that the password column is long enough to contain a password hash (and consider renaming the column to reflect that it contains a hash).
• The "room" table needs four additional columns to keep track of equipment and notes.
• A "log" table needs to be created, containing log_id, log_date, ip_address, event_type and event_details columns.