Venue Booking System

Welcome to the Venue Booking System repository! This application allows users to securely book venues like tutorial rooms, classrooms, boardrooms, and auditoriums. It's designed to be intuitive and efficient, while also ensuring that administrators can manage bookings effectively.

This project utilizes a secure login system, password hashing, and robust database management to make venue booking smooth for all users. Whether you're an admin or a regular user, this system has features tailored to your needs.

About the Project
Tech Stack
Features
Database Schema
Setup and Installation
Usage
How to Contribute
License

About the Project

This Venue Booking System is a web-based application that allows users to log in, select a venue, choose a time, and submit a booking request for approval by the admin. It ensures that booking data is well-managed, past bookings are archived, and real-time availability is tracked for each venue.

Key Features:

User Authentication: Users can securely log in with password hashing.
Venue Booking: Choose from various venues and times, and submit a request.
Admin Console: Manage bookings, approve or deny requests, and view past bookings.
Database Management: Uses MySQL for data storage and queries.

The system is designed using Python with Tkinter for the front end, MySQL for the database backend, and bcrypt for password hashing.

Tech Stack

Frontend:
- Python (with Tkinter for GUI)
- CustomTkinter (for modern UI elements)
Backend:
- MySQL (for storing user data, bookings, and venues)
- Python (for backend logic)
Database:
- MySQL database to store user data, venue details, and booking records.
- bcrypt for password hashing and validation.
Libraries:
- customtkinter (for customizable UI components)
- mysql.connector (to interact with MySQL)
- tkinter (for building the application’s interface)

Features

Login Page:
- Users log in with their user ID and password.
- Admin users and regular users have different privileges.
- Passwords are securely hashed before storing in the database for security.
User Console:
- After login, users can view their current bookings.
- Users can submit new booking requests for venues.
Admin Console:
- Admins can approve or deny booking requests.
- Admins can view all past bookings and archive old data.
- Admins can easily manage venue availability and bookings.
Venue Booking:
- Users can select a venue, choose a time slot, and submit a booking request.
- The application validates the booking time against existing bookings for conflicts.
Secure Data Storage:
- User credentials (passwords) are securely stored using bcrypt hashing.
- All other data is stored in MySQL and can be queried and modified through the application.

Database Schema

The project uses a MySQL database with the following key tables:

1. Users

Stores user information such as login credentials and roles.

Column Name	Type	Description
user_id	varchar(10)	Primary Key, Unique User ID
user_name	varchar(50)	User’s full name
pswd	varchar(255)	Hashed password
email_id	varchar(60)	Unique email ID
role	enum('user', 'admin')	Role (either 'user' or 'admin')

2. Venues

Stores details of each venue available for booking.

Column Name	Type	Description
venue_id	varchar(4)	Primary Key, Unique Venue ID
venue_name	varchar(50)	Venue name (e.g., Auditorium, Classroom)
type	enum('Classroom', 'Auditorium', 'Lecture Theatre', 'Tutorial Room', 'Meeting Room', 'Laboratory')	Type of venue (classroom, auditorium, etc.)
capacity	smallint	Venue seating capacity
location	varchar(50)	Venue location (e.g., building, floor)

3. Booking Requests

Tracks the booking requests made by users.

Column Name	Type	Description
booking_id	int	Primary Key, Unique Booking ID
user_id	varchar(10)	Foreign Key to `Users`
venue_id	varchar(4)	Foreign Key to `Venues`
date	date	Date of the booking
start_time	time	Start time of the booking
end_time	time	End time of the booking (optional)
status	enum('pending', 'approved', 'rejected')	Booking status (pending, approved, rejected)

4. Booking Logs

Stores past booking data for archival purposes.

Column Name	Type	Description
log_id	int	Primary Key, Unique Log ID
user_id	varchar(10)	Foreign Key to `Users`
venue_id	varchar(4)	Foreign Key to `Venues`
booking_date	date	Date of the booking
start_time	time	Start time of the booking
end_time	time	End time of the booking
status	enum('Expired', 'Canceled')	Status of the booking log (Expired, Canceled)

5. Approved Bookings

Stores data on approved booking requests.

Column Name	Type	Description
approval_id	int	Primary Key, Unique Approval ID
admin_id	varchar(10)	Foreign Key to `Users` (admin who approved the booking)
approval_date	date	Date of approval
user_id	varchar(10)	Foreign Key to `Users`
venue_id	varchar(4)	Foreign Key to `Venues`
booking_date	date	Date of the booking
start_time	time	Start time of the booking
end_time	time	End time of the booking (optional)
status	enum('Approved', 'Rejected')	Status of the approved booking (Approved, Rejected)
comments	varchar(1024)	Optional comments on the booking approval

Setup and Installation

Follow the steps below to set up the Venue Booking System locally:

1. Clone the Repository

Clone the repository to your local machine:

git clone https://github.com/yourusername/venue-booking-system.git

2. Install Dependencies

Ensure you have Python 3.x installed. Then install the necessary dependencies:

pip install -r requirements.txt

3. Database Setup

Install MySQL and create a database named venue_booking_system.
Create the necessary tables as per the schema above.

Run the following script to set up your MySQL database:

CREATE DATABASE venue_booking_system;
USE venue_booking_system;

-- Create Users Table
CREATE TABLE users (
    user_id VARCHAR(10) PRIMARY KEY,
    user_name VARCHAR(50) NOT NULL,
    pswd VARCHAR(255) NOT NULL,
    email_id VARCHAR(60) NOT NULL UNIQUE,
    role ENUM('user', 'admin') NOT NULL
);

-- Create Venues Table
CREATE TABLE venues (
    venue_id VARCHAR(4) PRIMARY KEY,
    venue_name VARCHAR(50) NOT NULL,
    type ENUM('Classroom', 'Auditorium', 'Lecture Theatre', 'Tutorial Room', 'Meeting Room', 'Laboratory') NOT NULL,
    capacity SMALLINT NOT NULL,
    location VARCHAR(50) NOT NULL
);

-- Create Booking Requests Table
CREATE TABLE booking_requests (
    booking_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id VARCHAR(10) NOT NULL,
    venue_id VARCHAR(4) NOT NULL,
    date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME,
    status ENUM('pending', 'approved', 'rejected') NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (venue_id) REFERENCES venues(venue_id) ON DELETE CASCADE
);

-- Create Approved Bookings Table
CREATE TABLE approved_bookings (
    approval_id INT AUTO_INCREMENT PRIMARY KEY,
    admin_id VARCHAR(10),
    approval_date DATE,
    user_id VARCHAR(10) NOT NULL,
    venue_id VARCHAR(4) NOT NULL,
    booking_date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME,
    status ENUM('Approved', 'Rejected'),
    comments VARCHAR(1024),
    FOREIGN KEY (admin_id) REFERENCES users(user_id) ON DELETE SET NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (venue_id) REFERENCES venues(venue_id) ON DELETE CASCADE
);

-- Create Booking Logs Table
CREATE TABLE booking_logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id VARCHAR(10),
    venue_id VARCHAR(4),
    booking_date DATE,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    status ENUM('Expired', 'Canceled') NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE SET NULL,
    FOREIGN KEY (venue_id) REFERENCES venues(venue_id) ON DELETE SET NULL
);

Implementing Scheduled Events in MySQL 8.0

To implement scheduled events in MySQL 8.0 using DataGrip, follow these steps:

Enable the Event Scheduler MySQL’s Event Scheduler must be enabled. To check and enable it:
- In DataGrip, open the Query Console for your MySQL database.
- Run the following query to check if the event scheduler is active:
```
SHOW VARIABLES LIKE 'event_scheduler';
```
- If the result shows OFF, enable it by running:
```
SET GLOBAL event_scheduler = ON;
```

Write the Scheduled Event A scheduled event needs to be defined in SQL. Here's an example to automate moving expired bookings from approved_bookings to booking_logs:

In the Query Console, write the following SQL to create the event:

CREATE EVENT move_expired_bookings
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
 INSERT INTO booking_logs (log_id, user_id, venue_id, booking_date, start_time, end_time, status)
 SELECT approval_id, user_id, venue_id, booking_date, start_time, end_time, 'Expired'
 FROM approved_bookings
 WHERE booking_date < CURDATE() 
 OR (booking_date = CURDATE() AND end_time < CURTIME());

 DELETE FROM approved_bookings
 WHERE booking_date < CURDATE() 
 OR (booking_date = CURDATE() AND end_time < CURTIME());
END;

Execute the query to create the event.

4. Configure Database Connection

In the Python code, ensure that the MySQL credentials in the common.py file are correctly set to your local MySQL database connection.

Usage

Run the Application
- Run the Python script to start the login page:
```
python login_page.py
```
- Follow the prompts to log in as a user or admin.
Admin Features
- Admin users can approve or deny booking requests and view past bookings.
User Features
- Users can view their current bookings and make new booking requests.

How to Contribute

We welcome contributions to the Venue Booking System! Here’s how you can contribute:

Fork the repository.
Clone your fork to your local machine.
Create a new branch:
```
git checkout -b feature-branch
```
Make your changes and commit:
```
git commit -m "Add new feature"
```
Push your changes:
```
git push origin feature-branch
```
Create a pull request to the main repository.

License

This project is licensed under the MIT License - see the LICENSE file for details.

Thank you for checking out the Venue Booking System! We hope this project helps simplify venue booking and management. Feel free to contribute and make it better! 😄

technovore007 / Venue-Booking-App

readme