Untitled

# 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_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()