Hotel Management System Project

Welcome to Hopiteri : a Hotel Management System project! This Python-based system is designed to assist hotel administrators in managing customer information, room bookings, and generating insightful reports. The project incorporates a MySQL database to store and retrieve data efficiently. It includes functionalities such as creating and updating customer details, booking rooms, visualizing booking statistics, and calculating total revenue.

---

Project Overview

Hopiteri offers a user-friendly interface for hotel staff to perform essential tasks seamlessly. With a variety of features, it aims to streamline operations and enhance the overall customer experience. The system utilizes the MySQL database for robust data storage and retrieval, ensuring data integrity and reliability.

• Project Overview
• Key Features
• Technology Stack
• How to Run
• Database Schema
• • customers Table
  • bookings Table
• Overview of Source Code
• • Import Libraries
  • Connection Establishment
  • Table Creation
  • Printing Hotel Details
  • Customer Creation
  • Customer Update
  • Viewing All Customers
  • Room Booking
  • Booking Update
  • Booking Deletion
  • Viewing All Bookings
  • Room Occupancy Summary
  • Calculating Total Revenue
  • Visualizing Room Bookings
  • Main Function
• Recommendations for Future Work
• Conclusion

---

Key Features

1. Customer Management:
   • Create new customer records with relevant details.
   • Update existing customer information for accuracy.
2. Room Booking:
   • Book rooms with specified check-in and check-out dates.
   • Update and delete existing bookings.
3. Data Visualization:
   • Visualize room booking statistics using bar charts.
   • View room occupancy summary to monitor hotel occupancy.
4. Financial Reporting:
   • Calculate and display total revenue generated from room bookings.

---

Technology Stack

• Programming Language: Python
• Database: MySQL
• Libraries: mysql.connector, pandas, matplotlib

---

How to Run

1. Ensure you have Python installed on your machine.
2. Install required libraries using pip install mysql-connector-python pandas matplotlib.
3. Set up a MySQL database and update the connection details in the code.
4. Run the script using python hotel_management_system.py.
5. Follow the on-screen prompts to interact with the system.

---

Database Schema

The database schema for Hopiteri consists of two tables: 'customers' and 'bookings'. Below is an overview of the schema:

• customers:
  • id (INT, AUTO_INCREMENT, PRIMARY KEY)
  • name (VARCHAR(255))
  • email (VARCHAR(255))
  • phone (VARCHAR(15))
• bookings:
  • id (INT, AUTO_INCREMENT, PRIMARY KEY)
  • check_in (DATE)
  • check_out (DATE)
  • room_type (VARCHAR(50))
  • num_guests (INT)
  • customer_id (INT, FOREIGN KEY to customers(id))
  • total_price (FLOAT)

---

Overview of Source Code

The source code for Hopiteri is organized into functions and sections, providing a modular structure. Key components include database connection, table creation, user interface, and various operations such as customer and booking management.

Import Libraries

import mysql.connector
import pandas as pd
import matplotlib.pyplot as plt
from mysql.connector.locales.eng import client_error
            

This block imports necessary libraries.

• mysql.connector: Used for interaction with MySQL databases.
• pandas: A data manipulation library, useful for working with tabular data.
• matplotlib.pyplot: Enables data visualization through various plotting functions.
• client_error from mysql.connector.locales.eng: Provides localization support for MySQL client errors.

Connection Establishment

def establish_connection():
    return mysql.connector.connect(user='sql12669536',
                                    password='**********',
                                    host='sql12.freesqldatabase.com',
                                    port='3306',
                                    database='sql12669536')
            

Defines a function establish_connection to connect to a MySQL database using specific credentials (username, password, host, port, and database name).

Table Creation

def create_tables():
    global conn, myc
    conn = establish_connection()
    myc = conn.cursor()

    # Create customers table
    myc.execute("""
        CREATE TABLE IF NOT EXISTS customers (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255),
            email VARCHAR(255),
            phone VARCHAR(15)
        )
    """)
    
    # Create bookings table
    myc.execute("""
        CREATE TABLE IF NOT EXISTS bookings (
            id INT AUTO_INCREMENT PRIMARY KEY,
            check_in DATE,
            check_out DATE,
            room_type VARCHAR(50),
            num_guests INT,
            customer_id INT,
            total_price FLOAT,
            FOREIGN KEY (customer_id) REFERENCES customers(id)
        )
    """)

    conn.commit()
                            

• Establishes a connection to the database and creates a cursor.
• Defines SQL statements to create 'customers' and 'bookings' tables if they don't exist.
• Commits the changes to the database.

Printing Hotel Details

def print_hotel_details():
    details = "\n" + "-"*50 + f"\n{'Welcome to Hospiteri : a hotel management software':^50}\n{'Your Home Away From Home!':^50}\n" + "-"*50
    print(details)
            

Generates and prints a welcome message for the hotel in a formatted manner.

Customer Creation

def create_customer():
    print("-"*50)
    name = input("Enter customer's name: ")
    email = input("Enter customer's email: ")
    phone = input("Enter customer's phone number: ")
    sql = "INSERT INTO customers (name, email, phone) VALUES ('{}', '{}', '{}')".format(name, email, phone)
    myc.execute(sql)
    conn.commit()
    print("\nExecution successful. Customer created.")
    print("-"*50)
            

• Takes user input for customer details.
• Constructs an SQL query to insert the customer into the 'customers' table.
• Executes the query and commits the changes.

Customer Update

def update_customer():
    print("-"*50)
    customer_id = int(input("Enter customer ID to update: "))
    new_name = input("Enter new name: ")
    new_email = input("Enter new email: ")
    new_phone = input("Enter new phone number: ")
            
    sql = "UPDATE customers SET name = '{}', email = '{}', phone = '{}' WHERE id = {}".format(
    new_name, new_email, new_phone, customer_id)
    myc.execute(sql)
    conn.commit()
    print("\nExecution successful. Customer updated.")
    print("-"*50)
            

• Takes user input for updating customer details.
• Constructs an SQL query to update the customer in the 'customers' table.
• Executes the query and commits the changes.

Viewing All Customers

def view_all_customers():
    print("-"*50)
    print("All Customers\n" + "-"*50)
            
    sql = "SELECT * FROM customers"
    myc.execute(sql)
    data = myc.fetchall()
            
    if not data:
        print("No customers found.")
    else:
        columns = [i[0] for i in myc.description]
        df = pd.DataFrame(data, columns=columns)
        print(df)
            
    print("-"*50)
            

• Fetches all customer records from the 'customers' table.
• Displays the data using pandas DataFrame.

Room Booking

def book_room():
    print("-"*50)
    print("Room Booking\n" + "-"*50)
    check_in = input("Enter check-in date (YYYY-MM-DD): ")
    check_out = input("Enter check-out date (YYYY-MM-DD): ")

    room_type = input("Enter preferred room type: ")
    num_guests = int(input("Enter number of guests: "))

    customer_id = int(input("Enter customer ID: "))

    sql = "INSERT INTO bookings (check_in, check_out, room_type, num_guests, customer_id) VALUES ('{}', '{}', '{}', {}, {})".format(
        check_in, check_out, room_type, num_guests, customer_id)
    myc.execute(sql)
    conn.commit()
    print("\nExecution successful. Room booked.")
    print("-"*50)
            

• Takes user input for room booking details.
• Constructs an SQL query to insert the booking into the 'bookings' table.
• Executes the query and commits the changes.

Booking Update

def update_booking():
    print("-"*50)
    booking_id = int(input("Enter booking ID to update: "))
    new_check_in = input("Enter new check-in date (YYYY-MM-DD): ")
    new_check_out = input("Enter new check-out date (YYYY-MM-DD): ")
    new_room_type = input("Enter new room type: ")
    new_num_guests = int(input("Enter new number of guests: "))

    sql = "UPDATE bookings SET check_in = '{}', check_out = '{}', room_type = '{}', num_guests = {} WHERE id = {}".format(
        new_check_in, new_check_out, new_room_type, new_num_guests, booking_id)
    myc.execute(sql)
    conn.commit()
    print("\nExecution successful. Booking updated.")
    print("-"*50)
            

• Takes user input for updating booking details.
• Constructs an SQL query to update the booking in the 'bookings' table.
• Executes the query and commits the changes.

Booking Deletion

def delete_booking():
    print("-"*50)
    booking_id = int(input("Enter booking ID to delete: "))

    sql = "DELETE FROM bookings WHERE id = {}".format(booking_id)
    myc.execute(sql)
    conn.commit()
    print("\nExecution successful. Booking deleted.")
    print("-"*50)
            

• Takes user input for the booking ID to delete.
• Constructs an SQL query to delete the booking from the 'bookings' table.
• Executes the query and commits the changes.

Viewing All Bookings

def view_all_bookings():
    print("-"*50)
    print("All Bookings\n" + "-"*50)

    sql = "SELECT * FROM bookings"
    myc.execute(sql)
    data = myc.fetchall()

    if not data:
        print("No bookings found.")
    else:
        columns = [i[0] for i in myc.description]
        df = pd.DataFrame(data, columns=columns)
        print(df)

    print("-"*50)
            

• Fetches all booking records from the 'bookings' table.
• Displays the data using pandas DataFrame.

Room Occupancy Summary

def view_room_occupancy():
    print("-"*50)
    print("Room Occupancy Summary\n" + "-"*50)

    sql = "SELECT room_type, COUNT(*) FROM bookings GROUP BY room_type"
    myc.execute(sql)
    data = myc.fetchall()

    if not data:
        print("No bookings found.")
    else:
        columns = ['Room Type', 'Occupancy']
        df = pd.DataFrame(data, columns=columns)
        print(df)

    print("-"*50)
            

• Fetches and displays room occupancy summary using pandas DataFrame.

Calculating Total Revenue

def calculate_total_revenue():
    print("-"*50)
    print("Calculating Total Revenue\n" + "-"*50)

    sql = "SELECT SUM(total_price) FROM bookings"
    myc.execute(sql)
    total_revenue = myc.fetchone()[0]

    if total_revenue is None:
        total_revenue = 0

    print(f"Total Revenue: {total_revenue} INR")
    print("-"*50)
            

• Calculates and prints the total revenue from all bookings.

Visualizing Room Bookings

def visualize_bookings():
    print("-"*50)
    print("Visualizing Room Bookings\n" + "-"*50)

    sql = "SELECT room_type, COUNT(*) FROM bookings GROUP BY room_type"
    myc.execute(sql)
    data = myc.fetchall()

    if not data:
        print("No bookings found.")
    else:
        room_types, counts = zip(*data)

        plt.bar(room_types, counts)
        plt.xlabel('Room Type')
        plt.ylabel('Number of Bookings')
        plt.title('Room Booking Statistics')
        plt.show()

    print("-"*50)
                

• Fetches data on room bookings.
• Visualizes room bookings using a bar chart with matplotlib.

Main Function

def main():
    create_tables()
    print("\n" + "-"*50)
    global conn, myc
    conn = establish_connection()
    myc = conn.cursor()
    o = "y"
    while o.lower() == "y":
        print_hotel_details()

        print("\nShortcut Options:\n" + "-"*50)
        print("X - Exit\nC - Create Customer\nU - Update Customer\nVAC - View All Customers\n
        B - Book Room\nUAC - Update Booking\nD - Delete Booking\nVAB - View All Bookings\n
        VR - View Room Occupancy\nG - Visualize Bookings\nTR - Calculate Total Revenue\n" + "-"*50)

        choice = input("Choose an option (C/U/VAC/B/UAC/D/VAB/VR/G/TR/X): ")

        if choice.lower() == "x":
            exit()
        elif choice.lower() == "c":
            create_customer()
        elif choice.lower() == "u":
            update_customer()
        elif choice.lower() == "vac":
            view_all_customers()
        elif choice.lower() == "b":
            book_room()
        elif choice.lower() == "uac":
            update_booking()
        elif choice.lower() == "d":
            delete_booking()
        elif choice.lower() == "vab":
            view_all_bookings()
        elif choice.lower() == "vr":
            view_room_occupancy()
        elif choice.lower() == "g":
            visualize_bookings()
        elif choice.lower() == "tr":
            calculate_total_revenue()
        else:
            print("Invalid option. Please choose again.")

        thanks = "\nThank you for choosing Hopiteri : a hotel management software!\n"
        print(thanks)
        print("-"*50)

        o = input("Do you want to continue (y/n): ")

if __name__ == "__main__":
    main()
                

• Calls functions to create tables and establish a connection.
• Enters a loop for user interaction based on menu choices.
• The main function orchestrates the entire flow of Hopiteri.

---

Recommendations for Future Work

As with any project, there are opportunities for future enhancements and expansions. Some recommendations for future work include:

1. Enhanced User Interface: Develop a more visually appealing and intuitive user interface for better user experience.
2. Additional Features: Integrate additional features such as online booking, employee management, and feedback systems to further streamline hotel operations.
3. Data Security Measures: Implement robust data security measures to ensure the protection and privacy of customer information.
4. Multi-Platform Compatibility: Adapt the system for compatibility across various platforms, including web and mobile applications.
5. Integration with External Services: Explore integration possibilities with external services such as payment gateways and third-party APIs to enhance functionality.

Continued development and refinement of Hopiteri will contribute to its effectiveness and adaptability in the dynamic hospitality industry.

---

Conclusion

In conclusion, Hopiteri project provides a comprehensive solution for efficient hotel administration and customer service. By leveraging Python, MySQL, and relevant libraries, the system delivers functionalities ranging from customer management to financial reporting. The user-friendly interface and visualization tools contribute to a seamless hotel management experience.