Untitled

import mysql.connector
from prettytable import PrettyTable
import time

# Database Connection
con = mysql.connector.connect(host='localhost', user='root', passwd='host1234')
cur = con.cursor()

try:
    cur.execute("CREATE DATABASE IF NOT EXISTS Restaurant_Management")
    cur.execute("USE Restaurant_Management")

    # Create Tables
    cur.execute("CREATE TABLE IF NOT EXISTS Menu (ItemID INT AUTO_INCREMENT PRIMARY KEY, ItemName VARCHAR(255), "
                "Category VARCHAR(255), Price INT)")

    cur.execute("CREATE TABLE IF NOT EXISTS Orders (OrderID INT AUTO_INCREMENT PRIMARY KEY, ItemID INT, "
                "CustomerName VARCHAR(255), OrderDate DATE, Quantity INT, Packaging VARCHAR(20), "
                "FOREIGN KEY (ItemID) REFERENCES Menu(ItemID))")

    cur.execute("CREATE TABLE IF NOT EXISTS Customer_Orders_Admin LIKE Orders")
    cur.execute("ALTER TABLE Customer_Orders_Admin ADD COLUMN Status VARCHAR(20) DEFAULT 'Pending'")

    # Insert Sample Data
    cur.execute("INSERT IGNORE INTO Menu VALUES (1, 'Burger', 'Fast Food', 10), "
                "(2, 'Pizza', 'Fast Food', 12), (3, 'Salad', 'Healthy', 8)")

    con.commit()

except mysql.connector.Error as err:
    print(f"Error: {err}")
    con.rollback()

# Display Menu
def display_menu():
    try:
        cur.execute("SELECT * FROM Menu")
        menu_items = cur.fetchall()
        table = PrettyTable(["Item ID", "Item Name", "Category", "Price"])
        for item in menu_items:
            table.add_row(item)
        print(table)

    except mysql.connector.Error as err:
        print(f"Error: {err}")
        con.rollback()

# Customer Menu
def login_as_customer_menu(customer_name):
    while True:
        print("\n\t\t\t\tCustomer Menu")
        y = int(input('''
1. Display Menu
2. Place Order
3. Display Your Orders
4. Modify Order
5. Exit
'''))
        if y == 1:
            display_menu()
        elif y == 2:
            place_order(customer_name)
        elif y == 3:
            display_customer_orders(customer_name)
        elif y == 4:
            modify_order(customer_name)
        elif y == 5:
            break
        else:
            print('Please enter a valid input. ')
# Place Order
def place_order(customer_name):
    try:
        display_menu()
        item_id = int(input("\nEnter the Item ID to order: "))
        quantity = int(input("Enter the quantity: "))
        packaging = input("Enter 'Dine-in' or 'Take away': ").capitalize()

        order_date = time.strftime('%Y-%m-%d')
        cur.execute('SELECT ItemID FROM Menu')
        data = cur.fetchall()
        
        if (item_id,) in data:
            cur.execute("INSERT INTO Orders (ItemID, CustomerName, OrderDate, Quantity, Packaging) "
                        "VALUES (%s, %s, %s, %s, %s)",
                        (item_id, customer_name, order_date, quantity, packaging))
            print(f"\nOrder placed successfully for {customer_name}!")
            con.commit()
        else:
            print('Item not found. ')

    except (mysql.connector.Error, ValueError) as err:
        print(f"Error: {err}")
        con.rollback()

# Display Customer Orders
def display_customer_orders(customer_name):
    try:
        cur.execute("SELECT Menu.ItemName, Orders.OrderDate, Orders.Quantity, Orders.Packaging FROM Orders "
                    "JOIN Menu ON Orders.ItemID = Menu.ItemID "
                    "WHERE CustomerName = %s", (customer_name,))
        orders = cur.fetchall()
        table = PrettyTable(["Item Name", "Order Date", "Quantity", "Packaging"])
        for order in orders:
            table.add_row(order)
        print(table)

    except mysql.connector.Error as err:
        print(f"Error: {err}")
        con.rollback()

# Modify Order
def modify_order(customer_name):
    try:
        display_customer_orders(customer_name)
        order_id = int(input("Enter Order ID to modify: "))
        cur.execute("SELECT OrderID FROM Orders WHERE CustomerName = %s", (customer_name,))
        data = cur.fetchall()

        if (order_id,) in data:
            new_quantity = int(input("Enter the new quantity: "))
            new_packaging = input("Enter 'Dine-in' or 'Take away': ").capitalize()

            cur.execute("UPDATE Orders SET Quantity = %s, Packaging = %s WHERE OrderID = %s",
                        (new_quantity, new_packaging, order_id))
            con.commit()
            print(f"Order ID {order_id} modified successfully!")
        else:
            print('Sorry, this order does not exist.')

    except (mysql.connector.Error, ValueError) as err:
        print(f"Error: {err}")
        con.rollback()
# Admin - View Orders with Status
def admin_view_orders_with_status():
    try:
        cur.execute("SELECT Menu.ItemName, Orders.CustomerName, Orders.OrderDate, "
                    "Orders.Quantity, Orders.Packaging, Customer_Orders_Admin.Status "
                    "FROM Orders "
                    "JOIN Menu ON Orders.ItemID = Menu.ItemID "
                    "JOIN Customer_Orders_Admin ON Orders.OrderID = Customer_Orders_Admin.OrderID")
        orders = cur.fetchall()
        table = PrettyTable(["Item Name", "Customer Name", "Order Date", "Quantity", "Packaging", "Status"])
        for order in orders:
            table.add_row(order)
        print(table)

    except mysql.connector.Error as err:
        print(f"Error: {err}")
        con.rollback()

# Admin - Change Order Status
def admin_change_order_status():
    try:
        admin_view_orders_with_status()
        order_id = int(input("Enter Order ID to change status: "))
        cur.execute("SELECT OrderID FROM Customer_Orders_Admin")
        data = cur.fetchall()

        if (order_id,) in data:
            new_status = input("Enter 'Pending' or 'Delivered': ").capitalize()

            cur.execute("UPDATE Customer_Orders_Admin SET Status = %s WHERE OrderID = %s",
                        (new_status, order_id))
            con.commit()
            print(f"Order ID {order_id} status changed to {new_status} successfully!")
        else:
            print('Sorry, this order does not exist in the admin records.')

    except (mysql.connector.Error, ValueError) as err:
        print(f"Error: {err}")
        con.rollback()

# Admin - Total Revenue with Status
def admin_total_revenue_with_status():
    try:
        cur.execute("SELECT SUM(Menu.Price * Orders.Quantity) FROM Menu "
                    "JOIN Orders ON Menu.ItemID = Orders.ItemID")
        total_revenue = cur.fetchone()[0]
        print(f"Total Revenue: ${total_revenue}")

    except mysql.connector.Error as err:
        print(f"Error: {err}")
        con.rollback()
# Admin - Statistics with Quantity
def admin_statistics_with_quantity():
    try:
        cur.execute("SELECT Menu.Category, COUNT(*), SUM(Orders.Quantity) "
                    "FROM Menu JOIN Orders ON Menu.ItemID = Orders.ItemID "
                    "GROUP BY Menu.Category")
        statistics = cur.fetchall()
        table = PrettyTable(["Category", "ItemCount", "Total Quantity"])
        for stat in statistics:
            table.add_row(stat)
        print(table)

    except mysql.connector.Error as err:
        print(f"Error: {err}")
        con.rollback()

# Customer - Modify Order
def customer_modify_order(customer_name):
    try:
        display_customer_orders(customer_name)
        order_id = int(input("Enter Order ID to modify: "))
        cur.execute("SELECT OrderID FROM Orders WHERE CustomerName = %s", (customer_name,))
        data = cur.fetchall()

        if (order_id,) in data:
            new_quantity = int(input("Enter the new quantity: "))
            new_packaging = input("Enter 'Dine-in' or 'Take away': ").capitalize()

            cur.execute("UPDATE Orders SET Quantity = %s, Packaging = %s WHERE OrderID = %s",
                        (new_quantity, new_packaging, order_id))
            con.commit()
            print(f"Order ID {order_id} modified successfully!")
        else:
            print('Sorry, this order does not exist for the customer.')

    except (mysql.connector.Error, ValueError) as err:
        print(f"Error: {err}")
        con.rollback()

# Customer Menu - Additional Options
def customer_menu_additional_options(customer_name):
    while True:
        print("\nAdditional Options:")
        y = int(input('''
1. Modify Order
2. Back to Customer Menu
'''))
        if y == 1:
            customer_modify_order(customer_name)
        elif y == 2:
            break
        else:
            print('Please enter a valid input. ')
# Admin - Customer Order Admin Table
def admin_customer_order_admin_table():
    try:
        cur.execute("CREATE TABLE IF NOT EXISTS Customer_Orders_Admin "
                    "(OrderID INT AUTO_INCREMENT PRIMARY KEY, ItemID INT, "
                    "CustomerName VARCHAR(255), OrderDate DATE, Quantity INT, Packaging VARCHAR(20), "
                    "Status VARCHAR(20), FOREIGN KEY (ItemID) REFERENCES Menu(ItemID))")

        cur.execute("INSERT INTO Customer_Orders_Admin (ItemID, CustomerName, OrderDate, Quantity, Packaging, Status) "
                    "SELECT * FROM Orders")

        con.commit()

    except mysql.connector.Error as err:
        print(f"Error: {err}")
        con.rollback()

# Admin - Change Order Status
def admin_change_order_status():
    try:
        admin_view_orders()
        order_id = int(input("Enter Order ID to change status: "))
        cur.execute("SELECT OrderID FROM Orders")
        data = cur.fetchall()

        if (order_id,) in data:
            new_status = input("Enter 'Pending' or 'Delivered': ").capitalize()
            cur.execute("UPDATE Orders SET Status = %s WHERE OrderID = %s", (new_status, order_id))
            con.commit()
            print(f"Order ID {order_id} status changed successfully!")
        else:
            print('Sorry, this order does not exist.')

    except (mysql.connector.Error, ValueError) as err:
        print(f"Error: {err}")
        con.rollback()
# Customer - Sign Up
def customer_sign_up():
    try:
        customer_name = input("Enter your name: ")
        cur.execute("INSERT INTO Customers (CustomerName) VALUES (%s)", (customer_name,))
        con.commit()
        print(f"Account created successfully for {customer_name}!")

    except mysql.connector.Error as err:
        print(f"Error: {err}")
        con.rollback()

# Customer - Log In
def customer_log_in():
    try:
        customer_name = input("Enter your name: ")
        cur.execute("SELECT CustomerName FROM Customers")
        data = cur.fetchall()

        if (customer_name,) in data:
            print(f"Welcome back, {customer_name}!")
        else:
            print("Account not found. Please sign up.")

    except mysql.connector.Error as err:
        print(f"Error: {err}")
        con.rollback()

# Customer - Modify Food Order
def customer_modify_food_order():
    try:
        customer_name = input("Enter your name: ")
        display_customer_orders(customer_name)
        order_id = int(input("Enter Order ID to modify: "))
        cur.execute("SELECT OrderID FROM Orders WHERE CustomerName = %s", (customer_name,))
        data = cur.fetchall()

        if (order_id,) in data:
            new_quantity = int(input("Enter the new quantity: "))
            cur.execute("UPDATE Orders SET Quantity = %s WHERE OrderID = %s", (new_quantity, order_id))
            con.commit()
            print(f"Order ID {order_id} modified successfully!")
        else:
            print('Sorry, this order does not exist.')

    except (mysql.connector.Error, ValueError) as err:
        print(f"Error: {err}")
        con.rollback()

# Admin - Display Customer Orders Admin
def admin_display_customer_orders_admin():
    try:
        cur.execute("SELECT Customer_Orders_Admin.OrderID, Menu.ItemName, "
                    "Customer_Orders_Admin.OrderDate, Customer_Orders_Admin.Quantity, "
                    "Customer_Orders_Admin.Packaging, Customer_Orders_Admin.Status "
                    "FROM Customer_Orders_Admin JOIN Menu ON Customer_Orders_Admin.ItemID = Menu.ItemID")
        orders = cur.fetchall()
        table = PrettyTable(["Order ID", "Item Name", "Order Date", "Quantity", "Packaging", "Status"])
        for order in orders:
            table.add_row(order)
        print(table)

    except mysql.connector.Error as err:
        print(f"Error: {err}")
        con.rollback()

# Admin - Modify Customer Orders Admin
def admin_modify_customer_orders_admin():
    try:
        admin_display_customer_orders_admin()
        order_id = int(input("Enter Order ID to modify: "))
        cur.execute("SELECT OrderID FROM Customer_Orders_Admin")
        data = cur.fetchall()

        if (order_id,) in data:
            new_quantity = int(input("Enter the new quantity: "))
            new_packaging = input("Enter 'Dine-in' or 'Take away': ").capitalize()
            cur.execute("UPDATE Customer_Orders_Admin SET Quantity = %s, Packaging = %s WHERE OrderID = %s",
                        (new_quantity, new_packaging, order_id))
            con.commit()
            print(f"Order ID {order_id} modified successfully!")
        else:
            print('Sorry, this order does not exist.')

    except (mysql.connector.Error, ValueError) as err:
        print(f"Error: {err}")
        con.rollback()
# Admin - Change Status of Customer Orders Admin
def admin_change_status_customer_orders_admin():
    try:
        admin_display_customer_orders_admin()
        order_id = int(input("Enter Order ID to change status: "))
        cur.execute("SELECT OrderID FROM Customer_Orders_Admin")
        data = cur.fetchall()

        if (order_id,) in data:
            new_status = input("Enter 'Pending' or 'Delivered': ").capitalize()
            cur.execute("UPDATE Customer_Orders_Admin SET Status = %s WHERE OrderID = %s",
                        (new_status, order_id))
            con.commit()
            print(f"Status of Order ID {order_id} changed to {new_status} successfully!")
        else:
            print('Sorry, this order does not exist.')

    except (mysql.connector.Error, ValueError) as err:
        print(f"Error: {err}")
        con.rollback()

# Customer - Place Order with Packaging Option
def customer_place_order_with_packaging(customer_name):
    try:
        display_menu()
        item_id = int(input("\nEnter the Item ID to order: "))
        quantity = int(input("Enter the quantity: "))
        packaging = input("Enter 'Dine-in' or 'Take away': ").capitalize()
        order_date = time.strftime('%Y-%m-%d')

        cur.execute('SELECT ItemID FROM Menu')
        data = cur.fetchall()

        if (item_id,) in data:
            cur.execute("INSERT INTO Orders (ItemID, CustomerName, OrderDate, Quantity, Packaging) "
                        "VALUES (%s, %s, %s, %s, %s)",
                        (item_id, customer_name, order_date, quantity, packaging))
            print(f"\nOrder placed successfully for {customer_name}!")
            con.commit()
        else:
            print('Item not found.')

    except (mysql.connector.Error, ValueError) as err:
        print(f"Error: {err}")
        con.rollback()

# Customer Menu - Modify Food Order
def customer_menu_modify_food_order(customer_name):
    while True:
        print("\n\t\t\t\tCustomer Menu")
        print("1. View Menu")
        print("2. Place Order")
        print("3. View Orders")
        print("4. Modify Food Order")
        print("5. Logout")

        choice = int(input("Enter your choice (1-5): "))
        if choice == 1:
            display_menu()
        elif choice == 2:
            customer_place_order_with_packaging(customer_name)
        elif choice == 3:
            display_customer_orders(customer_name)
        elif choice == 4:
            customer_modify_food_order()
        elif choice == 5:
            break
        else:
            print("Invalid input! Please choose a valid option.")

# Main Function
def main():
    while True:
        print("Ｗ ＥＬＣＯＭＥ ＴＯ  X Y Z   R E S T A U R A N T")
        print("1. Login as Admin")
        print("2. Login as Customer")
        print("3. Exit\n")

        user_type = input("Enter your choice (1-3): ")

        if user_type == '1':
            admin_menu()
        elif user_type == '2':
            print("1. Login")
            print("2. Sign Up")
            login_choice = input("Enter your choice (1-2): ")

            if login_choice == '1':
                customer_log_in()
                customer_menu_modify_food_order()
            elif login_choice == '2':
                customer_sign_up()
            else:
                print("Invalid input! Please choose a valid option.")
        elif user_type == '3':
            print("Thank you for using the Restaurant Management System!")
            break
        else:
            print("Invalid input! Please choose a valid option.")

main()
#CBSE BOARD PROJECT
#DESIGNED BY TEJA SAI # 12A