databaseApplicationSystem/database application system.py

import mysql.connector
from datetime import datetime
import tkinter as tk
from tkinter import messagebox, simpledialog
import random

# 数据库配置
db_config = {
    'host': 'localhost',
    'user': 'root',
    'password': '123456',
    'database': 'mydata'
}

# 连接到数据库
conn = mysql.connector.connect(**db_config)
cursor = conn.cursor()

def select_table():
    cursor.execute("SELECT * FROM tables WHERE Cz_zt = '空闲'")
    available_tables = cursor.fetchall()
    if not available_tables:
        messagebox.showinfo("提示", "没有空桌子可用。")
        return None

    table_selection = tk.Toplevel()
    table_selection.title("选择桌子")
    tk.Label(table_selection, text="欢迎光临！请选择一个空闲的桌子：", font=("Arial", 14)).pack(pady=10)

    # 创建表格显示桌子信息
    table_frame = tk.Frame(table_selection)
    table_frame.pack(pady=10)

    for table in available_tables:
        table_button = tk.Button(
            table_frame,
            text=f"桌号: {table[1]}\n位置: {random.choice(['靠窗', '中央', '角落'])}",
            width=20,
            height=2,
            bg="lightblue",
            command=lambda t=table: proceed_to_customer_info(t[1], t[0], table_selection)
        )
        table_button.pack(pady=5, padx=10, side="top")

def proceed_to_customer_info(cz_number, cz_id, table_selection):
    cursor.execute("UPDATE tables SET Cz_zt = '占用' WHERE Cz_id = %s", (cz_id,))
    conn.commit()
    table_selection.destroy()
    main_window.cz_id = cz_id
    messagebox.showinfo("桌子已选择", f"您已选择桌子编号: {cz_number}。")
    add_customer()  # 自动弹出填写顾客信息的窗口

def add_customer():
    customer_window = tk.Toplevel()
    customer_window.title("填写顾客信息")

    tk.Label(customer_window, text="请输入您的信息：", font=("Arial", 14)).pack(pady=10)

    tk.Label(customer_window, text="姓名：").pack()
    name_entry = tk.Entry(customer_window)
    name_entry.pack()

    tk.Label(customer_window, text="性别：").pack()
    sex_entry = tk.Entry(customer_window)
    sex_entry.pack()

    tk.Label(customer_window, text="电话：").pack()
    phone_entry = tk.Entry(customer_window)
    phone_entry.pack()

    def confirm_customer():
        t_name = name_entry.get()
        t_rsex = sex_entry.get()
        t_phone = phone_entry.get()
        if t_name and t_rsex and t_phone:
            cursor.execute("INSERT INTO customers (T_name, T_rsex, T_phone) VALUES (%s, %s, %s)", (t_name, t_rsex, t_phone))
            conn.commit()
            customer_window.destroy()
            main_window.t_id = cursor.lastrowid
            place_order(main_window.t_id, main_window.cz_id)  # 跳转到点餐流程
        else:
            messagebox.showwarning("警告", "请完整填写所有信息！")

    tk.Button(customer_window, text="确认", command=confirm_customer).pack(pady=10)

def place_order(t_id, cz_id):
    cursor.execute("SELECT * FROM menu_items")
    menu_items = cursor.fetchall()
    order_window = tk.Toplevel()
    order_window.title("点餐")
    tk.Label(order_window, text="菜单：", font=("Arial", 14)).pack(pady=10)
    selected_items = []

    item_vars = []
    for item in menu_items:
        item_var = tk.BooleanVar()
        tk.Checkbutton(order_window, text=f"{item[1]} - {item[3]}元", variable=item_var).pack(anchor="w")
        item_vars.append((item_var, item[0]))

    def confirm_order():
        for var, item_id in item_vars:
            if var.get():
                selected_items.append(item_id)
        if selected_items:
            # 自动分配空闲服务员
            cursor.execute("SELECT W_id, W_name FROM workers WHERE W_stat = 0 LIMIT 1")
            worker = cursor.fetchone()
            if worker:
                w_id, w_name = worker
                # 更新服务员状态为忙碌
                cursor.execute("UPDATE workers SET W_stat = 1 WHERE W_id = %s", (w_id,))
                conn.commit()

                time = datetime.now()
                items_str = ','.join(selected_items)
                cursor.execute(
                    "INSERT INTO orders (T_id, time, Cz_id, W_id, items) VALUES (%s, %s, %s, %s, %s)",
                    (t_id, time, cz_id, w_id, items_str)
                )
                order_id = cursor.lastrowid
                conn.commit()

                # 显示分配的服务员信息
                messagebox.showinfo("订单成功", f"您的服务员是：{w_name} (ID: {w_id})")
                order_window.destroy()
                main_window.order_id = order_id
                main_window.selected_items = selected_items
                generate_bill(main_window.order_id, main_window.selected_items)  # 自动生成账单
            else:
                messagebox.showwarning("提示", "当前没有空闲的服务员，请稍候再试！")
        else:
            messagebox.showwarning("警告", "请选择至少一个菜品。")

    tk.Button(order_window, text="确认点餐", command=confirm_order).pack()
    order_window.mainloop()

def generate_bill(order_id, selected_items):
    total_price = 0
    total_price_after_discount = 0
    for m_id in selected_items:
        cursor.execute("SELECT M_price FROM menu_items WHERE M_id = %s", (m_id,))
        price = cursor.fetchone()[0]
        cursor.execute("SELECT discount FROM discount_rules WHERE M_id = %s", (m_id,))
        discount = cursor.fetchone()
        if discount and discount[0] is not None:
            discounted_price = price * discount[0]
        else:
            discounted_price = price
        total_price += price
        total_price_after_discount += discounted_price

    s_price = total_price
    s_priceafter = total_price_after_discount
    time = datetime.now()
    cursor.execute("INSERT INTO bills (O_id, S_price, S_priceafter, time) VALUES (%s, %s, %s, %s)",
                   (order_id, s_price, s_priceafter, time))
    conn.commit()

    bill_window = tk.Toplevel()
    bill_window.title("账单")
    tk.Label(bill_window, text=f"总价: {s_price}元", font=("Arial", 14)).pack(pady=10)
    tk.Label(bill_window, text=f"折后价: {s_priceafter}元", font=("Arial", 14)).pack(pady=10)

    def confirm_bill():
        cursor.execute("UPDATE tables SET Cz_zt = '空闲' WHERE Cz_id = (SELECT Cz_id FROM orders WHERE O_id = %s)", (order_id,))
        conn.commit()
        messagebox.showinfo("提示", "账单已确认，感谢您的光临，桌子已重置为空闲。")
        bill_window.destroy()

    tk.Button(bill_window, text="确认账单", command=confirm_bill).pack(pady=10)
    bill_window.mainloop()

def show_customers():
    def edit_customer(t_id):
        cursor.execute("SELECT * FROM customers WHERE T_id = %s", (t_id,))
        customer = cursor.fetchone()
        if customer:
            edit_window = tk.Toplevel()
            edit_window.title("编辑顾客信息")

            tk.Label(edit_window, text="姓名：").pack()
            name_entry = tk.Entry(edit_window)
            name_entry.insert(0, customer[1])
            name_entry.pack()

            tk.Label(edit_window, text="性别：").pack()
            sex_entry = tk.Entry(edit_window)
            sex_entry.insert(0, customer[2])
            sex_entry.pack()

            tk.Label(edit_window, text="电话：").pack()
            phone_entry = tk.Entry(edit_window)
            phone_entry.insert(0, customer[3])
            phone_entry.pack()

            def confirm_edit():
                t_name = name_entry.get()
                t_rsex = sex_entry.get()
                t_phone = phone_entry.get()
                if t_name and t_rsex and t_phone:
                    cursor.execute("UPDATE customers SET T_name = %s, T_rsex = %s, T_phone = %s WHERE T_id = %s", (t_name, t_rsex, t_phone, t_id))
                    conn.commit()
                    edit_window.destroy()
                    show_customers()  # 刷新顾客信息
                else:
                    messagebox.showwarning("警告", "请完整填写所有信息！")

            tk.Button(edit_window, text="确认编辑", command=confirm_edit).pack(pady=10)

    def delete_customer(t_id):
        if messagebox.askyesno("确认删除", "确定要删除该顾客信息吗？"):
            # 先删除 orders 表中与该顾客相关的所有记录
            cursor.execute("DELETE FROM orders WHERE T_id = %s", (t_id,))
            conn.commit()
            
            # 再删除 customers 表中的记录
            cursor.execute("DELETE FROM customers WHERE T_id = %s", (t_id,))
            conn.commit()
            
            show_customers()  # 刷新顾客信息

    cursor.execute("SELECT * FROM customers")
    customers = cursor.fetchall()
    if not customers:
        messagebox.showinfo("提示", "没有顾客信息。")
        return

    customer_window = tk.Toplevel()
    customer_window.title("顾客信息")

    for customer in customers:
        frame = tk.Frame(customer_window)
        frame.pack(pady=5, fill=tk.X)
        tk.Label(frame, text=f"ID: {customer[0]}, 姓名: {customer[1]}, 性别: {customer[2]}, 电话: {customer[3]}", width=40).pack(side=tk.LEFT)
        tk.Button(frame, text="编辑", command=lambda c=customer[0]: edit_customer(c), width=10).pack(side=tk.RIGHT, padx=5)
        tk.Button(frame, text="删除", command=lambda c=customer[0]: delete_customer(c), width=10).pack(side=tk.RIGHT, padx=5)

def show_workers():
    def edit_worker(w_id):
        cursor.execute("SELECT * FROM workers WHERE W_id = %s", (w_id,))
        worker = cursor.fetchone()
        if worker:
            edit_window = tk.Toplevel()
            edit_window.title("编辑员工信息")

            tk.Label(edit_window, text="姓名：").pack()
            name_entry = tk.Entry(edit_window)
            name_entry.insert(0, worker[1])
            name_entry.pack()

            tk.Label(edit_window, text="性别：").pack()
            sex_entry = tk.Entry(edit_window)
            sex_entry.insert(0, worker[2])
            sex_entry.pack()

            tk.Label(edit_window, text="状态：").pack()
            status_entry = tk.Entry(edit_window)
            status_entry.insert(0, worker[3])
            status_entry.pack()

            tk.Label(edit_window, text="工资：").pack()
            salary_entry = tk.Entry(edit_window)
            salary_entry.insert(0, worker[4])
            salary_entry.pack()

            def confirm_edit():
                w_name = name_entry.get()
                w_sex = sex_entry.get()
                w_stat = status_entry.get()
                w_salary = salary_entry.get()
                if w_name and w_sex and w_stat and w_salary:
                    cursor.execute("UPDATE workers SET W_name = %s, W_sex = %s, W_stat = %s, W_salary = %s WHERE W_id = %s", (w_name, w_sex, w_stat, w_salary, w_id))
                    conn.commit()
                    edit_window.destroy()
                    show_workers()  # 刷新员工信息
                else:
                    messagebox.showwarning("警告", "请完整填写所有信息！")

            tk.Button(edit_window, text="确认编辑", command=confirm_edit).pack(pady=10)

    def delete_worker(w_id):
        if messagebox.askyesno("确认删除", "确定要删除该员工信息吗？"):
            # 先删除 orders 表中与该员工相关的所有记录
            cursor.execute("DELETE FROM orders WHERE W_id = %s", (w_id,))
            conn.commit()
            
            # 再删除 workers 表中的记录
            cursor.execute("DELETE FROM workers WHERE W_id = %s", (w_id,))
            conn.commit()
            
            show_workers()  # 刷新员工信息

    cursor.execute("SELECT * FROM workers")
    workers = cursor.fetchall()
    if not workers:
        messagebox.showinfo("提示", "没有员工信息。")
        return

    worker_window = tk.Toplevel()
    worker_window.title("员工信息")

    for worker in workers:
        frame = tk.Frame(worker_window)
        frame.pack(pady=5, fill=tk.X)
        tk.Label(frame, text=f"ID: {worker[0]}, 姓名: {worker[1]}, 性别: {worker[2]}, 状态: {worker[3]}, 工资: {worker[4]}", width=50).pack(side=tk.LEFT)
        tk.Button(frame, text="编辑", command=lambda w=worker[0]: edit_worker(w), width=10).pack(side=tk.RIGHT, padx=5)
        tk.Button(frame, text="删除", command=lambda w=worker[0]: delete_worker(w), width=10).pack(side=tk.RIGHT, padx=5)

def show_menu_items():
    def edit_menu_item(m_id):
        cursor.execute("SELECT * FROM menu_items WHERE M_id = %s", (m_id,))
        item = cursor.fetchone()
        if item:
            edit_window = tk.Toplevel()
            edit_window.title("编辑菜单项")

            tk.Label(edit_window, text="名称：").pack()
            name_entry = tk.Entry(edit_window)
            name_entry.insert(0, item[1])
            name_entry.pack()

            tk.Label(edit_window, text="类型：").pack()
            type_entry = tk.Entry(edit_window)
            type_entry.insert(0, item[2])
            type_entry.pack()

            tk.Label(edit_window, text="价格：").pack()
            price_entry = tk.Entry(edit_window)
            price_entry.insert(0, item[3])
            price_entry.pack()

            def confirm_edit():
                m_name = name_entry.get()
                m_type = type_entry.get()
                m_price = price_entry.get()
                if m_name and m_type and m_price:
                    cursor.execute("UPDATE menu_items SET M_name = %s, M_type = %s, M_price = %s WHERE M_id = %s", (m_name, m_type, m_price, m_id))
                    conn.commit()
                    edit_window.destroy()
                    show_menu_items()  # 刷新菜单信息
                else:
                    messagebox.showwarning("警告", "请完整填写所有信息！")

            tk.Button(edit_window, text="确认编辑", command=confirm_edit).pack(pady=10)

    def delete_menu_item(m_id):
        if messagebox.askyesno("确认删除", "确定要删除该菜单项吗？"):
            # 先删除 discount_rules 表中与该菜单项相关的所有记录
            cursor.execute("DELETE FROM discount_rules WHERE M_id = %s", (m_id,))
            conn.commit()
            
            # 再删除 menu_items 表中的记录
            cursor.execute("DELETE FROM menu_items WHERE M_id = %s", (m_id,))
            conn.commit()
            
            show_menu_items()  # 刷新菜单信息

    cursor.execute("SELECT * FROM menu_items")
    menu_items = cursor.fetchall()
    if not menu_items:
        messagebox.showinfo("提示", "没有菜单信息。")
        return

    menu_window = tk.Toplevel()
    menu_window.title("菜单信息")

    for item in menu_items:
        frame = tk.Frame(menu_window)
        frame.pack(pady=5, fill=tk.X)
        tk.Label(frame, text=f"ID: {item[0]}, 名称: {item[1]}, 类型: {item[2]}, 价格: {item[3]}", width=50).pack(side=tk.LEFT)
        tk.Button(frame, text="编辑", command=lambda i=item[0]: edit_menu_item(i), width=10).pack(side=tk.RIGHT, padx=5)
        tk.Button(frame, text="删除", command=lambda i=item[0]: delete_menu_item(i), width=10).pack(side=tk.RIGHT, padx=5)

def show_bills():
    def view_bill_details(b_id):
        cursor.execute("SELECT * FROM bills WHERE B_id = %s", (b_id,))
        bill = cursor.fetchone()
        if bill:
            detail_window = tk.Toplevel()
            detail_window.title("账单详情")
            tk.Label(detail_window, text=f"账单ID: {bill[0]}", font=("Arial", 14)).pack(pady=10)
            tk.Label(detail_window, text=f"订单ID: {bill[1]}", font=("Arial", 14)).pack(pady=10)
            tk.Label(detail_window, text=f"总价: {bill[2]} 元", font=("Arial", 14)).pack(pady=10)
            tk.Label(detail_window, text=f"折后价: {bill[3]} 元", font=("Arial", 14)).pack(pady=10)
            tk.Label(detail_window, text=f"时间: {bill[4]}", font=("Arial", 14)).pack(pady=10)

    cursor.execute("SELECT * FROM bills")
    bills = cursor.fetchall()
    if not bills:
        messagebox.showinfo("提示", "没有账单信息。")
        return

    bill_window = tk.Toplevel()
    bill_window.title("账单信息")

    for bill in bills:
        frame = tk.Frame(bill_window)
        frame.pack(pady=5, fill=tk.X)
        tk.Label(frame, text=f"ID: {bill[0]}, 订单ID: {bill[1]}, 总价: {bill[2]}, 折后价: {bill[3]}, 时间: {bill[4]}", width=70).pack(side=tk.LEFT)
        tk.Button(frame, text="查看详情", command=lambda b=bill[0]: view_bill_details(b), width=10).pack(side=tk.RIGHT, padx=5)

def show_orders():
    def view_order_details(o_id):
        cursor.execute("SELECT * FROM orders WHERE O_id = %s", (o_id,))
        order = cursor.fetchone()
        if order:
            detail_window = tk.Toplevel()
            detail_window.title("订单详情")
            tk.Label(detail_window, text=f"订单ID: {order[0]}", font=("Arial", 14)).pack(pady=10)
            tk.Label(detail_window, text=f"顾客ID: {order[1]}", font=("Arial", 14)).pack(pady=10)
            tk.Label(detail_window, text=f"时间: {order[2]}", font=("Arial", 14)).pack(pady=10)
            tk.Label(detail_window, text=f"桌号: {order[3]}", font=("Arial", 14)).pack(pady=10)
            tk.Label(detail_window, text=f"服务员ID: {order[4]}", font=("Arial", 14)).pack(pady=10)
            tk.Label(detail_window, text=f"菜品: {order[5]}", font=("Arial", 14)).pack(pady=10)

    def delete_order(o_id):
        if messagebox.askyesno("确认删除", "确定要删除该订单吗？"):
            cursor.execute("DELETE FROM orders WHERE O_id = %s", (o_id,))
            conn.commit()
            show_orders()  # 刷新订单信息

    cursor.execute("SELECT * FROM orders")
    orders = cursor.fetchall()
    if not orders:
        messagebox.showinfo("提示", "没有订单信息。")
        return

    order_window = tk.Toplevel()
    order_window.title("订单信息")

    for order in orders:
        frame = tk.Frame(order_window)
        frame.pack(pady=5, fill=tk.X)
        tk.Label(frame, text=f"ID: {order[0]}, 顾客ID: {order[1]}, 时间: {order[2]}, 桌号: {order[3]}, 服务员ID: {order[4]}, 菜品: {order[5]}", width=70).pack(side=tk.LEFT)
        tk.Button(frame, text="查看详情", command=lambda o=order[0]: view_order_details(o), width=10).pack(side=tk.RIGHT, padx=5)
        tk.Button(frame, text="删除", command=lambda o=order[0]: delete_order(o), width=10).pack(side=tk.RIGHT, padx=5)

def show_discounts():
    def edit_discount(m_id):
        cursor.execute("SELECT * FROM discount_rules WHERE M_id = %s", (m_id,))
        discount = cursor.fetchone()
        if discount:
            edit_window = tk.Toplevel()
            edit_window.title("编辑优惠规则")

            tk.Label(edit_window, text="优惠：").pack()
            discount_entry = tk.Entry(edit_window)
            discount_entry.insert(0, discount[1])
            discount_entry.pack()

            def confirm_edit():
                d_discount = discount_entry.get()
                if d_discount:
                    cursor.execute("UPDATE discount_rules SET discount = %s WHERE M_id = %s", (d_discount, m_id))
                    conn.commit()
                    edit_window.destroy()
                    show_discounts()  # 刷新优惠信息
                else:
                    messagebox.showwarning("警告", "请完整填写所有信息！")

            tk.Button(edit_window, text="确认编辑", command=confirm_edit).pack(pady=10)

    def delete_discount(m_id):
        if messagebox.askyesno("确认删除", "确定要删除该优惠规则吗？"):
            cursor.execute("DELETE FROM discount_rules WHERE M_id = %s", (m_id,))
            conn.commit()
            show_discounts()  # 刷新优惠信息

    cursor.execute("SELECT * FROM discount_rules")
    discounts = cursor.fetchall()
    if not discounts:
        messagebox.showinfo("提示", "没有优惠信息。")
        return

    discount_window = tk.Toplevel()
    discount_window.title("优惠信息")

    for discount in discounts:
        frame = tk.Frame(discount_window)
        frame.pack(pady=5, fill=tk.X)
        tk.Label(frame, text=f"ID: {discount[0]}, 优惠: {discount[1]}", width=30).pack(side=tk.LEFT)
        tk.Button(frame, text="编辑", command=lambda d=discount[0]: edit_discount(d), width=10).pack(side=tk.RIGHT, padx=5)
        tk.Button(frame, text="删除", command=lambda d=discount[0]: delete_discount(d), width=10).pack(side=tk.RIGHT, padx=5)

def admin_panel():
    admin_window = tk.Toplevel()
    admin_window.title("管理员面板")

    tk.Button(admin_window, text="顾客信息", command=show_customers, width=20, height=2).pack(pady=10)
    tk.Button(admin_window, text="员工信息", command=show_workers, width=20, height=2).pack(pady=10)
    tk.Button(admin_window, text="菜单", command=show_menu_items, width=20, height=2).pack(pady=10)
    tk.Button(admin_window, text="账单", command=show_bills, width=20, height=2).pack(pady=10)
    tk.Button(admin_window, text="订单", command=show_orders, width=20, height=2).pack(pady=10)
    tk.Button(admin_window, text="优惠信息", command=show_discounts, width=20, height=2).pack(pady=10)

def main():
    global main_window
    main_window = tk.Tk()
    main_window.title("餐厅系统")

    tk.Button(main_window, text="顾客", command=select_table, width=20, height=2).pack(pady=10)
    tk.Button(main_window, text="管理员", command=admin_panel, width=20, height=2).pack(pady=10)

    main_window.mainloop()

if __name__ == "__main__":
    main()