一、项目背景
物业管理系统在现代城市化进程中起着至关重要的作用。 随着居民生活水平的提高和信息技术的迅猛发展，传统的物业管理模式已不能满足业主和管理者的需求。 为了提高管理效率、降低运营成本、提升服务质量，设计并实现一个集成化、智能化的物业管理系统显得尤为重要。 本项目旨在通过信息化手段，整合房屋、水电、费用等信息，为物业管理提供全面的技术支持。
二、用户需求
（一）普通用户
1、登录后查看个人信息、房间信息及IC卡信息。
2、查询所住房间的水表、电表读数及相关费用。
（二）管理员
1、管理所有用户信息，查询、添加、修改和删除用户及其房间、水电表、费用等信息。
2、通过用户信息查询相关房间和IC卡信息。
3、通过房间信息查询相关费用信息。
4、管理和维护系统数据的完整性和安全性。
三、功能需求
1、用户登录：普通用户和管理员分别通过各自的用户名和密码进行登录。
2、用户信息管理：用户可以查看和更新个人信息，管理员可以管理所有用户的信息。
3、房间管理：包括房间的添加、修改和删除。
4、水电表管理：包括水表和电表的添加、修改和删除。
5、费用管理：包括费用的添加、查询、和修改。
6、IC卡管理：包括IC卡的添加、修改、和查询。
四、数据库设计
（一）数据库需求分析
根据系统功能需求，数据库需包含以下几张主要的表：
1、用户表（User）：存储用户的基本信息。
2、房间表（Room）：存储房间信息。
3、水表表（WaterMeter）：存储水表信息。
4、电表表（ElectricityMeter）：存储电表信息。
5、费用记录表（FeeRecord）：存储费用信息。
6、IC卡表（ICCard）：存储IC卡信息。
（二）数据库概念结构设计
1、用户表（User）：
CREATE TABLE User (
UserID INT PRIMARY KEY AUTO_INCREMENT,
Username VARCHAR(50) NOT NULL,
Password VARCHAR(50) NOT NULL,
ContactInfo VARCHAR(100),
Address VARCHAR(200),
UserType ENUM('管理员', '普通用户') NOT NULL
);
2、房间表（Room）：
CREATE TABLE Room (
RoomID INT PRIMARY KEY AUTO_INCREMENT,
RoomNumber VARCHAR(10) NOT NULL,
Floor INT NOT NULL,
RoomType ENUM('公寓', '办公室') NOT NULL,
UserID INT,
FOREIGN KEY (UserID) REFERENCES User(UserID)
);
3、水表表（WaterMeter）：
CREATE TABLE WaterMeter (
WaterMeterID INT PRIMARY KEY AUTO_INCREMENT,
WaterMeterNumber VARCHAR(20) NOT NULL,
RoomID INT UNIQUE,
CurrentReading DECIMAL(10, 2) NOT NULL,
LastReadingDate DATE NOT NULL,
FOREIGN KEY (RoomID) REFERENCES Room(RoomID)
);
4、电表表（ElectricityMeter）：
CREATE TABLE ElectricityMeter (
ElectricityMeterID INT PRIMARY KEY AUTO_INCREMENT,
ElectricityMeterNumber VARCHAR(20) NOT NULL,
RoomID INT UNIQUE,
CurrentReading DECIMAL(10, 2) NOT NULL,
LastReadingDate DATE NOT NULL,
FOREIGN KEY (RoomID) REFERENCES Room(RoomID)
);
5、费用记录表（FeeRecord）：
CREATE TABLE FeeRecord (
FeeRecordID INT PRIMARY KEY AUTO_INCREMENT,
RoomID INT,
WaterFee DECIMAL(10, 2) NOT NULL,
ElectricityFee DECIMAL(10, 2) NOT NULL,
ICCardID INT,
PaymentDate DATE NOT NULL,
PaymentStatus ENUM('已缴费', '未缴费') NOT NULL,
FOREIGN KEY (RoomID) REFERENCES Room(RoomID),
FOREIGN KEY (ICCardID) REFERENCES ICCard(ICCardID)
);
6、IC卡表（ICCard）：
CREATE TABLE ICCard (
ICCardID INT PRIMARY KEY AUTO_INCREMENT,
CardNumber VARCHAR(20) NOT NULL,
UserID INT,
Balance DECIMAL(10, 2) NOT NULL,
IssueDate DATE NOT NULL,
LastRechargeDate DATE NOT NULL,
FOREIGN KEY (UserID) REFERENCES User(UserID)
);
（三）数据库逻辑结构设计
数据库表之间的关系如下：
1、用户（User）与房间（Room）之间存在一对多（1:N）的关系。
2、房间（Room）与水表（WaterMeter）和电表（ElectricityMeter）之间存在一对一（1：1）的关系。
3、房间（Room）与费用记录（FeeRecord）之间存在一对多（1：N）的关系。
4、用户（User）与IC卡（ICCard）之间存在一对多（1;N）的关系。
5、IC卡（ICCard）与费用记录（FeeRecord）之间存在一对多（1:N）的关系。
（四）E-R图
五、系统实现
1、数据库连接管理
使用了contextlib.contextmanager装饰器定义了一个connect_db函数来管理数据库连接，确保连接在使用后正确关闭。
@contextmanager
def connect_db():
try:
cnx = mysql.connector.connect(
host='localhost',
user='root',
password='123',
database='propertymanagement'
)
cursor = cnx.cursor()
yield cursor
cnx.commit()
except Error as e:
messagebox.showerror("数据库错误", f"错误: {e}")
finally:
cursor.close()
cnx.close()
2、多线程处理
使用了threading库和run_in_thread装饰器来异步执行数据库操作，以避免在执行数据库查询时阻塞GUI界面。
def run_in_thread(func):
def wrapper(*args, **kwargs):
threading.Thread(target=func, args=args, kwargs=kwargs).start()
return wrapper
3、用户登录功能
实现普通用户和管理员的登录功能，通过用户名和密码验证用户身份。
普通用户：
def user_login():
login_window = tk.Toplevel()
login_window.title("用户登录")
tk.Label(login_window, text="用户名:").pack()
username_entry = tk.Entry(login_window)
username_entry.pack()
tk.Label(login_window, text="密码:").pack()
password_entry = tk.Entry(login_window, show="*")
password_entry.pack()
@run_in_thread
def login():
username = username_entry.get()
password = password_entry.get()
with connect_db() as cursor:
query = "SELECT UserID FROM User WHERE Username = %s AND Password = %s AND UserType = '普通用户'"
cursor.execute(query, (username, password))
user = cursor.fetchone()
if user:
user_id = user[0]
login_window.destroy()
user_interface(user_id)
else:
messagebox.showerror("登录失败", "用户名或密码错误")
tk.Button(login_window, text="登录", command=login).pack()
管理员：
def admin_login():
login_window = tk.Toplevel()
login_window.title("管理员登录")
tk.Label(login_window, text="用户名:").pack()
username_entry = tk.Entry(login_window)
username_entry.pack()
tk.Label(login_window, text="密码:").pack()
password_entry = tk.Entry(login_window, show="*")
password_entry.pack()
@run_in_thread
def login():
username = username_entry.get()
password = password_entry.get()
with connect_db() as cursor:
query = "SELECT UserID FROM User WHERE Username = %s AND Password = %s AND UserType = '管理员'"
cursor.execute(query, (username, password))
user = cursor.fetchone()
if user:
login_window.destroy()
admin_interface()
else:
messagebox.showerror("登录失败", "用户名或密码错误")
tk.Button(login_window, text="登录", command=login).pack()
4、用户信息管理功能
普通用户登录后可以查看和更新个人信息，管理员可以管理所有用户的信息，包括添加、修改和删除用户信息。
用户信息界面：
def user_interface(user_id):
user_window = tk.Toplevel()
user_window.title("用户信息")
@run_in_thread
def load_user_info():
with connect_db() as cursor:
query = "SELECT Username, ContactInfo, Address FROM User WHERE UserID = %s"
cursor.execute(query, (user_id,))
user_info = cursor.fetchone()
if user_info:
username, contact_info, address = user_info
tk.Label(user_window, text=f"用户名: {username}").pack()
tk.Label(user_window, text=f"联系方式: {contact_info}").pack()
tk.Label(user_window, text=f"地址: {address}").pack()
tk.Button(user_window, text="修改信息", command=lambda: edit_user_info(user_id)).pack()
query = "SELECT RoomNumber FROM Room WHERE UserID = %s"
cursor.execute(query, (user_id,))
rooms = cursor.fetchall()
room_numbers = [room[0] for room in rooms]
tk.Label(user_window, text="房间信息:").pack()
for room_number in room_numbers:
tk.Label(user_window, text=room_number).pack()
query = "SELECT ICCardID, CardNumber, Balance FROM ICCard WHERE UserID = %s"
cursor.execute(query, (user_id,))
iccards = cursor.fetchall()
tk.Label(user_window, text="IC卡信息:").pack()
for iccard_id, card_number, balance in iccards:
card_frame = tk.Frame(user_window)
card_frame.pack()
tk.Label(card_frame, text=f"卡号: {card_number}, 余额: {balance}").pack()
tk.Button(card_frame, text="查询消费记录", command=lambda cid=iccard_id: query_fee_records_by_iccard(cid, False)).pack()
load_user_info()
tk.Button(user_window, text="查询房间费用", command=lambda: query_user_fees(user_id)).pack()
管理员主界面：
def admin_interface():
admin_window = tk.Toplevel()
admin_window.title("管理员界面")
tk.Button(admin_window, text="查看所有用户信息", command=show_all_users).pack()
tk.Button(admin_window, text="查询用户详细信息", command=query_user_info).pack()
tk.Button(admin_window, text="新增信息", command=add_info).pack()
查询所有用户信息：
def show_all_users():
users_window = tk.Toplevel()
users_window.title("所有用户信息")
@run_in_thread
def load_users():
with connect_db() as cursor:
query = "SELECT UserID, Username, ContactInfo, Address FROM User WHERE UserType = '普通用户'"
cursor.execute(query)
users = cursor.fetchall()
for user in users:
user_id, username, contact_info, address = user
user_frame = tk.Frame(users_window)
user_frame.pack()
tk.Label(user_frame, text=f"用户名: {username}, 联系方式: {contact_info}, 地址: {address}").pack()
tk.Button(user_frame, text="详细信息", command=lambda uid=user_id: show_user_detail(uid)).pack()
tk.Button(user_frame, text="删除用户", command=lambda uid=user_id: delete_user(uid, user_frame)).pack()
load_users()
查询用户信息：
def query_user_info():
query_window = tk.Toplevel()
query_window.title("查询用户信息")
tk.Label(query_window, text="输入用户名或用户ID:").pack()
query_entry = tk.Entry(query_window)
query_entry.pack()
@run_in_thread
def search():
query_value = query_entry.get()
with connect_db() as cursor:
query = "SELECT UserID FROM User WHERE Username = %s OR UserID = %s"
cursor.execute(query, (query_value, query_value))
user = cursor.fetchone()
if user:
user_id = user[0]
query_window.destroy()
show_user_detail(user_id)
else:
messagebox.showerror("查询失败", "没有找到匹配的用户")
tk.Button(query_window, text="查询", command=search).pack()
查看用户详细信息：
def show_user_detail(user_id):
detail_window = tk.Toplevel()
detail_window.title("用户详细信息")
@run_in_thread
def load_user_detail():
with connect_db() as cursor:
query = "SELECT Username, ContactInfo, Address FROM User WHERE UserID = %s"
cursor.execute(query, (user_id,))
user_info = cursor.fetchone()
if user_info:
username, contact_info, address = user_info
tk.Label(detail_window, text=f"用户名: {username}").pack()
tk.Label(detail_window, text=f"联系方式: {contact_info}").pack()
tk.Label(detail_window, text=f"地址: {address}").pack()
tk.Button(detail_window, text="修改用户信息", command=lambda: edit_user_info(user_id)).pack()
query = "SELECT RoomID, RoomNumber FROM Room WHERE UserID = %s"
cursor.execute(query, (user_id,))
rooms = cursor.fetchall()
tk.Label(detail_window, text="房间信息:").pack()
for room_id, room_number in rooms:
room_frame = tk.Frame(detail_window)
room_frame.pack()
tk.Label(room_frame, text=room_number).pack()
tk.Button(room_frame, text="查询费用", command=lambda rid=room_id: show_fees(rid, True)).pack()
tk.Button(room_frame, text="修改房间信息", command=lambda rid=room_id: edit_room_info(rid)).pack()
query = "SELECT ICCardID, CardNumber, Balance FROM ICCard WHERE UserID = %s"
cursor.execute(query, (user_id,))
iccards = cursor.fetchall()
tk.Label(detail_window, text="IC卡信息:").pack()
for iccard_id, card_number, balance in iccards:
card_frame = tk.Frame(detail_window)
card_frame.pack()
tk.Label(card_frame, text=f"卡号: {card_number}, 余额: {balance}").pack()
tk.Button(card_frame, text="修改IC卡信息", command=lambda cid=iccard_id: edit_iccard_info(cid)).pack()
tk.Button(card_frame, text="查询消费记录", command=lambda cid=iccard_id: query_fee_records_by_iccard(cid, True)).pack()
load_user_detail()
编辑用户信息：
def edit_user_info(user_id):
edit_window = tk.Toplevel()
edit_window.title("修改用户信息")
@run_in_thread
def load_user_info():
with connect_db() as cursor:
query = "SELECT Username, ContactInfo, Address FROM User WHERE UserID = %s"
cursor.execute(query, (user_id,))
user_info = cursor.fetchone()
if user_info:
username, contact_info, address = user_info
tk.Label(edit_window, text="用户名:").grid(row=0, column=0)
username_entry = tk.Entry(edit_window)
username_entry.insert(0, username)
username_entry.grid(row=0, column=1)
tk.Label(edit_window, text="联系方式:").grid(row=1, column=0)
contact_entry = tk.Entry(edit_window)
contact_entry.insert(0, contact_info)
contact_entry.grid(row=1, column=1)
tk.Label(edit_window, text="地址:").grid(row=2, column=0)
address_entry = tk.Entry(edit_window)
address_entry.insert(0, address)
address_entry.grid(row=2, column=1)
@run_in_thread
def update_user_info():
new_username = username_entry.get()
new_contact_info = contact_entry.get()
new_address = address_entry.get()
with connect_db() as cursor:
update_query = "UPDATE User SET Username = %s, ContactInfo = %s, Address = %s WHERE UserID = %s"
cursor.execute(update_query, (new_username, new_contact_info, new_address, user_id))
edit_window.destroy()
messagebox.showinfo("成功", "用户信息已更新")
tk.Button(edit_window, text="更新信息", command=update_user_info).grid(row=3, column=0, columnspan=2)
load_user_info()
删除用户：
def delete_user(user_id, user_frame):
response = messagebox.askyesno("确认删除", "确定要删除该用户吗？")
if response:
with connect_db() as cursor:
delete_query = "DELETE FROM User WHERE UserID = %s"
cursor.execute(delete_query, (user_id,))
user_frame.destroy()
messagebox.showinfo("成功", "用户已删除")
新增信息：
def add_info():
add_window = tk.Toplevel()
add_window.title("新增信息")
tk.Label(add_window, text="选择要新增的信息类型:").pack()
info_type = tk.StringVar()
tk.Radiobutton(add_window, text="用户信息", variable=info_type, value="user").pack()
tk.Radiobutton(add_window, text="房间信息", variable=info_type, value="room").pack()
tk.Radiobutton(add_window, text="IC卡信息", variable=info_type, value="iccard").pack()
def open_add_info_window():
if info_type.get() == "user":
add_user_info()
elif info_type.get() == "room":
add_room_info()
elif info_type.get() == "iccard":
add_iccard_info()
tk.Button(add_window, text="确定", command=open_add_info_window).pack()
新增用户信息：
def add_user_info():
add_window = tk.Toplevel()
add_window.title("新增用户信息")
tk.Label(add_window, text="用户名:").grid(row=0, column=0)
username_entry = tk.Entry(add_window)
username_entry.grid(row=0, column=1)
tk.Label(add_window, text="密码:").grid(row=1, column=0)
password_entry = tk.Entry(add_window, show="*")
password_entry.grid(row=1, column=1)
tk.Label(add_window, text="联系方式:").grid(row=2, column=0)
contact_entry = tk.Entry(add_window)
contact_entry.grid(row=2, column=1)
tk.Label(add_window, text="地址:").grid(row=3, column=0)
address_entry = tk.Entry(add_window)
address_entry.grid(row=3, column=1)
def add_user():
username = username_entry.get()
password = password_entry.get()
contact_info = contact_entry.get()
address = address_entry.get()
with connect_db() as cursor:
insert_query = "INSERT INTO User (Username, Password, ContactInfo, Address, UserType) VALUES (%s, %s, %s, %s, '普通用户')"
cursor.execute(insert_query, (username, password, contact_info, address))
add_window.destroy()
messagebox.showinfo("成功", "用户信息已新增")
tk.Button(add_window, text="新增用户", command=add_user).grid(row=4, column=0, columnspan=2)
5、房间管理功能
管理员可以管理房间信息，包括房间的查询、添加、修改和删除。
管理员主界面添加房间管理功能：
def admin_interface():
admin_window = tk.Toplevel()
admin_window.title("管理员界面")
tk.Button(admin_window, text="查看所有用户信息", command=show_all_users).pack()
tk.Button(admin_window, text="查询用户详细信息", command=query_user_info).pack()
tk.Button(admin_window, text="管理房间信息", command=manage_rooms).pack()
tk.Button(admin_window, text="新增信息", command=add_info).pack()
管理房间信息界面：
def manage_rooms():
rooms_window = tk.Toplevel()
rooms_window.title("房间管理")
tk.Button(rooms_window, text="查看所有房间", command=show_all_rooms).pack()
tk.Button(rooms_window, text="查询房间信息", command=query_room_info).pack()
tk.Button(rooms_window, text="新增房间", command=add_room_info).pack()
查看所有房间信息：
def show_all_rooms():
all_rooms_window = tk.Toplevel()
all_rooms_window.title("所有房间信息")
@run_in_thread
def load_rooms():
with connect_db() as cursor:
query = "SELECT RoomID, RoomNumber, Floor, RoomType, UserID FROM Room"
cursor.execute(query)
rooms = cursor.fetchall()
for room in rooms:
room_id, room_number, floor, room_type, user_id = room
room_frame = tk.Frame(all_rooms_window)
room_frame.pack()
tk.Label(room_frame, text=f"房间号: {room_number}, 楼层: {floor}, 类型: {room_type}, 用户ID: {user_id}").pack()
tk.Button(room_frame, text="修改房间信息", command=lambda rid=room_id: edit_room_info(rid)).pack()
tk.Button(room_frame, text="删除房间", command=lambda rid=room_id: delete_room(rid, room_frame)).pack()
load_rooms()
查询房间信息：
def query_room_info():
query_window = tk.Toplevel()
query_window.title("查询房间信息")
tk.Label(query_window, text="输入房间号或房间ID:").pack()
query_entry = tk.Entry(query_window)
query_entry.pack()
@run_in_thread
def search():
query_value = query_entry.get()
with connect_db() as cursor:
query = "SELECT RoomID FROM Room WHERE RoomNumber = %s OR RoomID = %s"
cursor.execute(query, (query_value, query_value))
room = cursor.fetchone()
if room:
room_id = room[0]
query_window.destroy()
show_room_detail(room_id)
else:
messagebox.showerror("查询失败", "没有找到匹配的房间")
tk.Button(query_window, text="查询", command=search).pack()
查看房间详细信息：
def show_room_detail(room_id):
detail_window = tk.Toplevel()
detail_window.title("房间详细信息")
@run_in_thread
def load_room_detail():
with connect_db() as cursor:
query = "SELECT RoomNumber, Floor, RoomType, UserID FROM Room WHERE RoomID = %s"
cursor.execute(query, (room_id,))
room_info = cursor.fetchone()
if room_info:
room_number, floor, room_type, user_id = room_info
tk.Label(detail_window, text=f"房间号: {room_number}").pack()
tk.Label(detail_window, text=f"楼层: {floor}").pack()
tk.Label(detail_window, text=f"类型: {room_type}").pack()
tk.Label(detail_window, text=f"用户ID: {user_id}").pack()
tk.Button(detail_window, text="修改房间信息", command=lambda: edit_room_info(room_id)).pack()
load_room_detail()
修改房间信息：
def edit_room_info(room_id):
edit_window = tk.Toplevel()
edit_window.title("修改房间信息")
@run_in_thread
def load_room_info():
with connect_db() as cursor:
query = "SELECT RoomNumber, Floor, RoomType, UserID FROM Room WHERE RoomID = %s"
cursor.execute(query, (room_id,))
room_info = cursor.fetchone()
if room_info:
room_number, floor, room_type, user_id = room_info
tk.Label(edit_window, text="房间号:").grid(row=0, column=0)
room_number_entry = tk.Entry(edit_window)
room_number_entry.insert(0, room_number)
room_number_entry.grid(row=0, column=1)
tk.Label(edit_window, text="楼层:").grid(row=1, column=0)
floor_entry = tk.Entry(edit_window)
floor_entry.insert(0, floor)
floor_entry.grid(row=1, column=1)
tk.Label(edit_window, text="房间类型:").grid(row=2, column=0)
room_type_entry = tk.Entry(edit_window)
room_type_entry.insert(0, room_type)
room_type_entry.grid(row=2, column=1)
tk.Label(edit_window, text="用户ID:").grid(row=3, column=0)
user_id_entry = tk.Entry(edit_window)
user_id_entry.insert(0, user_id)
user_id_entry.grid(row=3, column=1)
def save_room_info():
new_room_number = room_number_entry.get()
new_floor = floor_entry.get()
new_room_type = room_type_entry.get()
new_user_id = user_id_entry.get()
@run_in_thread
def update_room_info():
with connect_db() as cursor:
query = "UPDATE Room SET RoomNumber = %s, Floor = %s, RoomType = %s, UserID = %s WHERE RoomID = %s"
cursor.execute(query, (new_room_number, new_floor, new_room_type, new_user_id, room_id))
messagebox.showinfo("成功", "房间信息已更新")
edit_window.destroy()
update_room_info()
tk.Button(edit_window, text="保存", command=save_room_info).grid(row=4, column=0, columnspan=2)
load_room_info()
删除房间：
def delete_room(room_id, frame):
if messagebox.askyesno("确认删除", "你确定要删除这个房间吗？"):
@run_in_thread
def remove_room():
with connect_db() as cursor:
query = "DELETE FROM Room WHERE RoomID = %s"
cursor.execute(query, (room_id,))
frame.destroy()
messagebox.showinfo("删除成功", "房间已成功删除")
remove_room()
新增房间信息：
def add_room_info():
add_window = tk.Toplevel()
add_window.title("新增房间信息")
tk.Label(add_window, text="房间号:").grid(row=0, column=0)
room_number_entry = tk.Entry(add_window)
room_number_entry.grid(row=0, column=1)
tk.Label(add_window, text="楼层:").grid(row=1, column=0)
floor_entry = tk.Entry(add_window)
floor_entry.grid(row=1, column=1)
tk.Label(add_window, text="房间类型:").grid(row=2, column=0)
room_type_entry = tk.Entry(add_window)
room_type_entry.grid(row=2, column=1)
tk.Label(add_window, text="用户ID:").grid(row=3, column=0)
user_id_entry = tk.Entry(add_window)
user_id_entry.grid(row=3, column=1)
def save_room():
room_number = room_number_entry.get()
floor = floor_entry.get()
room_type = room_type_entry.get()
user_id = user_id_entry.get()
@run_in_thread
def insert_room():
with connect_db() as cursor:
query = "INSERT INTO Room (RoomNumber, Floor, RoomType, UserID) VALUES (%s, %s, %s, %s)"
cursor.execute(query, (room_number, floor, room_type, user_id))
messagebox.showinfo("成功", "房间已新增")
add_window.destroy()
insert_room()
tk.Button(add_window, text="保存", command=save_room).grid(row=4, column=0, columnspan=2)
6、水电表管理功能
管理员可以管理水表和电表信息，包括添加、修改和删除水电表信息。
管理员主界面添加水电表管理功能：
def admin_interface():
admin_window = tk.Toplevel()
admin_window.title("管理员界面")
tk.Button(admin_window, text="查看所有用户信息", command=show_all_users).pack()
tk.Button(admin_window, text="查询用户详细信息", command=query_user_info).pack()
tk.Button(admin_window, text="管理房间信息", command=manage_rooms).pack()
tk.Button(admin_window, text="管理水电表信息", command=manage_meters).pack()
tk.Button(admin_window, text="新增信息", command=add_info).pack()
管理水电表信息界面：
def manage_meters():
meters_window = tk.Toplevel()
meters_window.title("水电表管理")
tk.Button(meters_window, text="查看所有水电表", command=show_all_meters).pack()
tk.Button(meters_window, text="查询水电表信息", command=query_meter_info).pack()
tk.Button(meters_window, text="新增水电表", command=add_meter_info).pack()
查看所有水电表信息：
def show_all_meters():
all_meters_window = tk.Toplevel()
all_meters_window.title("所有水电表信息")
@run_in_thread
def load_meters():
with connect_db() as cursor:
query = """
SELECT wm.WaterMeterID, wm.WaterMeterNumber, wm.RoomID, wm.CurrentReading, wm.LastReadingDate
FROM WaterMeter wm
UNION
SELECT em.ElectricityMeterID, em.ElectricityMeterNumber, em.RoomID, em.CurrentReading, em.LastReadingDate
FROM ElectricityMeter em
"""
cursor.execute(query)
meters = cursor.fetchall()
for meter in meters:
meter_id, meter_number, room_id, current_reading, last_reading_date = meter
meter_frame = tk.Frame(all_meters_window)
meter_frame.pack()
tk.Label(meter_frame, text=f"表号: {meter_number}, 房间ID: {room_id}, 当前读数: {current_reading}, 最后读数日期: {last_reading_date}").pack()
tk.Button(meter_frame, text="修改水电表信息", command=lambda mid=meter_id: edit_meter_info(mid)).pack()
tk.Button(meter_frame, text="删除水电表", command=lambda mid=meter_id: delete_meter(mid, meter_frame)).pack()
load_meters()
查询水电表信息：
def query_meter_info():
query_window = tk.Toplevel()
query_window.title("查询水电表信息")
tk.Label(query_window, text="输入水电表号或表ID:").pack()
query_entry = tk.Entry(query_window)
query_entry.pack()
@run_in_thread
def search():
query_value = query_entry.get()
with connect_db() as cursor:
query = """
SELECT WaterMeterID, WaterMeterNumber FROM WaterMeter WHERE WaterMeterNumber = %s OR WaterMeterID = %s
UNION
SELECT ElectricityMeterID, ElectricityMeterNumber FROM ElectricityMeter WHERE ElectricityMeterNumber = %s OR ElectricityMeterID = %s
"""
cursor.execute(query, (query_value, query_value, query_value, query_value))
meter = cursor.fetchone()
if meter:
meter_id = meter[0]
query_window.destroy()
show_meter_detail(meter_id)
else:
messagebox.showerror("查询失败", "没有找到匹配的水电表")
tk.Button(query_window, text="查询", command=search).pack()
查询水电表详细信息：
def show_meter_detail(meter_id):
detail_window = tk.Toplevel()
detail_window.title("水电表详细信息")
@run_in_thread
def load_meter_detail():
with connect_db() as cursor:
query = """
SELECT WaterMeterNumber, RoomID, CurrentReading, LastReadingDate FROM WaterMeter WHERE WaterMeterID = %s
UNION
SELECT ElectricityMeterNumber, RoomID, CurrentReading, LastReadingDate FROM ElectricityMeter WHERE ElectricityMeterID = %s
"""
cursor.execute(query, (meter_id, meter_id))
meter_info = cursor.fetchone()
if meter_info:
meter_number, room_id, current_reading, last_reading_date = meter_info
tk.Label(detail_window, text=f"表号: {meter_number}").pack()
tk.Label(detail_window, text=f"房间ID: {room_id}").pack()
tk.Label(detail_window, text=f"当前读数: {current_reading}").pack()
tk.Label(detail_window, text=f"最后读数日期: {last_reading_date}").pack()
tk.Button(detail_window, text="修改水电表信息", command=lambda: edit_meter_info(meter_id)).pack()
load_meter_detail()
修改水电表信息：
def edit_meter_info(meter_id):
edit_window = tk.Toplevel()
edit_window.title("修改水电表信息")
@run_in_thread
def load_meter_info():
with connect_db() as cursor:
query = """
SELECT WaterMeterNumber, RoomID, CurrentReading, LastReadingDate FROM WaterMeter WHERE WaterMeterID = %s
UNION
SELECT ElectricityMeterNumber, RoomID, CurrentReading, LastReadingDate FROM ElectricityMeter WHERE ElectricityMeterID = %s
"""
cursor.execute(query, (meter_id, meter_id))
meter_info = cursor.fetchone()
if meter_info:
meter_number, room_id, current_reading, last_reading_date = meter_info
tk.Label(edit_window, text="表号:").grid(row=0, column=0)
meter_number_entry = tk.Entry(edit_window)
meter_number_entry.insert(0, meter_number)
meter_number_entry.grid(row=0, column=1)
tk.Label(edit_window, text="房间ID:").grid(row=1, column=0)
room_id_entry = tk.Entry(edit_window)
room_id_entry.insert(0, room_id)
room_id_entry.grid(row=1, column=1)
tk.Label(edit_window, text="当前读数:").grid(row=2, column=0)
current_reading_entry = tk.Entry(edit_window)
current_reading_entry.insert(0, current_reading)
current_reading_entry.grid(row=2, column=1)
tk.Label(edit_window, text="最后读数日期:").grid(row=3, column=0)
last_reading_date_entry = tk.Entry(edit_window)
last_reading_date_entry.insert(0, last_reading_date)
last_reading_date_entry.grid(row=3, column=1)
def save_meter_info():
new_meter_number = meter_number_entry.get()
new_room_id = room_id_entry.get()
new_current_reading = current_reading_entry.get()
new_last_reading_date = last_reading_date_entry.get()
@run_in_thread
def update_meter_info():
with connect_db() as cursor:
if meter_info[0] == "WaterMeterNumber":
query = "UPDATE WaterMeter SET WaterMeterNumber = %s, RoomID = %s, CurrentReading = %s, LastReadingDate = %s WHERE WaterMeterID = %s"
else:
query = "UPDATE ElectricityMeter SET ElectricityMeterNumber = %s, RoomID = %s, CurrentReading = %s, LastReadingDate = %s WHERE ElectricityMeterID = %s"
cursor.execute(query, (new_meter_number, new_room_id, new_current_reading, new_last_reading_date, meter_id))
messagebox.showinfo("成功", "水电表信息已更新")
edit_window.destroy()
update_meter_info()
tk.Button(edit_window, text="保存", command=save_meter_info).grid(row=4, column=0, columnspan=2)
load_meter_info()
删除水电表信息：
def delete_meter(meter_id, frame):
if messagebox.askyesno("确认删除", "你确定要删除这个水电表吗？"):
@run_in_thread
def remove_meter():
with connect_db() as cursor:
query = "DELETE FROM WaterMeter WHERE WaterMeterID = %s"
cursor.execute(query, (meter_id,))
query = "DELETE FROM ElectricityMeter WHERE ElectricityMeterID = %s"
cursor.execute(query, (meter_id,))
frame.destroy()
messagebox.showinfo("删除成功", "水电表已成功删除")
remove_meter()
新增水电表信息：
def add_meter_info():
add_window = tk.Toplevel()
add_window.title("新增水电表信息")
tk.Label(add_window, text="表类型:").grid(row=0, column=0)
meter_type_var = tk.StringVar()
tk.Radiobutton(add_window, text="水表", variable=meter_type_var, value="water").grid(row=0, column=1)
tk.Radiobutton(add_window, text="电表", variable=meter_type_var, value="electricity").grid(row=0, column=2)
tk.Label(add_window, text="表号:").grid(row=1, column=0)
meter_number_entry = tk.Entry(add_window)
meter_number_entry.grid(row=1, column=1)
tk.Label(add_window, text="房间ID:").grid(row=2, column=0)
room_id_entry = tk.Entry(add_window)
room_id_entry.grid(row=2, column=1)
tk.Label(add_window, text="当前读数:").grid(row=3, column=0)
current_reading_entry = tk.Entry(add_window)
current_reading_entry.grid(row=3, column=1)
tk.Label(add_window, text="最后读数日期:").grid(row=4, column=0)
last_reading_date_entry = tk.Entry(add_window)
last_reading_date_entry.grid(row=4, column=1)
def save_meter():
meter_type = meter_type_var.get()
meter_number = meter_number_entry.get()
room_id = room_id_entry.get()
current_reading = current_reading_entry.get()
last_reading_date = last_reading_date_entry.get()
@run_in_thread
def insert_meter():
with connect_db() as cursor:
if meter_type == "water":
query = "INSERT INTO WaterMeter (WaterMeterNumber, RoomID, CurrentReading, LastReadingDate) VALUES (%s, %s, %s, %s)"
else:
query = "INSERT INTO ElectricityMeter (ElectricityMeterNumber, RoomID, CurrentReading, LastReadingDate) VALUES (%s, %s, %s, %s)"
cursor.execute(query, (meter_number, room_id, current_reading, last_reading_date))
messagebox.showinfo("成功", "水电表已新增")
add_window.destroy()
insert_meter()
tk.Button(add_window, text="保存", command=save_meter).grid(row=5, column=0, columnspan=2)
7、费用管理功能
记录和查询各房间的水电费用信息，管理员可以修改费用信息。
管理费用信息界面：
def manage_fees():
fees_window = tk.Toplevel()
fees_window.title("费用管理")
tk.Button(fees_window, text="查看所有费用记录", command=show_all_fees).pack()
tk.Button(fees_window, text="查询房间费用", command=query_fee_info).pack()
tk.Button(fees_window, text="新增费用记录", command=add_fee_info).pack()
查看所有费用记录：
def show_all_fees():
all_fees_window = tk.Toplevel()
all_fees_window.title("所有费用记录")
@run_in_thread
def load_fees():
with connect_db() as cursor:
query = """
SELECT fr.FeeRecordID, fr.RoomID, fr.WaterFee, fr.ElectricityFee, fr.PaymentDate, fr.PaymentStatus
FROM FeeRecord fr
"""
cursor.execute(query)
fees = cursor.fetchall()
for fee in fees:
fee_id, room_id, water_fee, electricity_fee, payment_date, payment_status = fee
fee_frame = tk.Frame(all_fees_window)
fee_frame.pack()
tk.Label(fee_frame, text=f"房间ID: {room_id}, 水费: {water_fee}, 电费: {electricity_fee}, 支付日期: {payment_date}, 支付状态: {payment_status}").pack()
tk.Button(fee_frame, text="修改费用信息", command=lambda fid=fee_id: edit_fee_info(fid)).pack()
tk.Button(fee_frame, text="删除费用记录", command=lambda fid=fee_id: delete_fee_record(fid, fee_frame)).pack()
load_fees()
查询房间费用信息：
def query_fee_info():
query_window = tk.Toplevel()
query_window.title("查询房间费用信息")
tk.Label(query_window, text="输入房间号或费用ID:").pack()
query_entry = tk.Entry(query_window)
query_entry.pack()
@run_in_thread
def search():
query_value = query_entry.get()
with connect_db() as cursor:
query = """
SELECT FeeRecordID FROM FeeRecord WHERE RoomID = %s OR FeeRecordID = %s
"""
cursor.execute(query, (query_value, query_value))
fee_record = cursor.fetchone()
if fee_record:
fee_record_id = fee_record[0]
query_window.destroy()
show_fee_detail(fee_record_id)
else:
messagebox.showerror("查询失败", "没有找到匹配的费用记录")
tk.Button(query_window, text="查询", command=search).pack()
查看费用详细信息：
def show_fee_detail(fee_record_id):
detail_window = tk.Toplevel()
detail_window.title("费用详细信息")
@run_in_thread
def load_fee_detail():
with connect_db() as cursor:
query = """
SELECT RoomID, WaterFee, ElectricityFee, PaymentDate, PaymentStatus
FROM FeeRecord
WHERE FeeRecordID = %s
"""
cursor.execute(query, (fee_record_id,))
fee_info = cursor.fetchone()
if fee_info:
room_id, water_fee, electricity_fee, payment_date, payment_status = fee_info
tk.Label(detail_window, text=f"房间ID: {room_id}").pack()
tk.Label(detail_window, text=f"水费: {water_fee}").pack()
tk.Label(detail_window, text=f"电费: {electricity_fee}").pack()
tk.Label(detail_window, text=f"支付日期: {payment_date}").pack()
tk.Label(detail_window, text=f"支付状态: {payment_status}").pack()
tk.Button(detail_window, text="修改费用信息", command=lambda: edit_fee_info(fee_record_id)).pack()
load_fee_detail()
修改费用信息：
def edit_fee_info(fee_record_id):
edit_window = tk.Toplevel()
edit_window.title("修改费用信息")
@run_in_thread
def load_fee_info():
with connect_db() as cursor:
query = """
SELECT RoomID, WaterFee, ElectricityFee, PaymentDate, PaymentStatus
FROM FeeRecord
WHERE FeeRecordID = %s
"""
cursor.execute(query, (fee_record_id,))
fee_info = cursor.fetchone()
if fee_info:
room_id, water_fee, electricity_fee, payment_date, payment_status = fee_info
tk.Label(edit_window, text="房间ID:").grid(row=0, column=0)
room_id_entry = tk.Entry(edit_window)
room_id_entry.insert(0, room_id)
room_id_entry.grid(row=0, column=1)
tk.Label(edit_window, text="水费:").grid(row=1, column=0)
water_fee_entry = tk.Entry(edit_window)
water_fee_entry.insert(0, water_fee)
water_fee_entry.grid(row=1, column=1)
tk.Label(edit_window, text="电费:").grid(row=2, column=0)
electricity_fee_entry = tk.Entry(edit_window)
electricity_fee_entry.insert(0, electricity_fee)
electricity_fee_entry.grid(row=2, column=1)
tk.Label(edit_window, text="支付日期:").grid(row=3, column=0)
payment_date_entry = tk.Entry(edit_window)
payment_date_entry.insert(0, payment_date)
payment_date_entry.grid(row=3, column=1)
tk.Label(edit_window, text="支付状态:").grid(row=4, column=0)
payment_status_entry = tk.Entry(edit_window)
payment_status_entry.insert(0, payment_status)
payment_status_entry.grid(row=4, column=1)
def save_fee_info():
new_room_id = room_id_entry.get()
new_water_fee = water_fee_entry.get()
new_electricity_fee = electricity_fee_entry.get()
new_payment_date = payment_date_entry.get()
new_payment_status = payment_status_entry.get()
@run_in_thread
def update_fee_info():
with connect_db() as cursor:
query = """
UPDATE FeeRecord
SET RoomID = %s, WaterFee = %s, ElectricityFee = %s, PaymentDate = %s, PaymentStatus = %s
WHERE FeeRecordID = %s
"""
cursor.execute(query, (new_room_id, new_water_fee, new_electricity_fee, new_payment_date, new_payment_status, fee_record_id))
messagebox.showinfo("成功", "费用信息已更新")
edit_window.destroy()
update_fee_info()
tk.Button(edit_window, text="保存", command=save_fee_info).grid(row=5, column=0, columnspan=2)
load_fee_info()
删除费用记录：
def delete_fee_record(fee_record_id, frame):
if messagebox.askyesno("确认删除", "你确定要删除这个费用记录吗？"):
@run_in_thread
def remove_fee_record():
with connect_db() as cursor:
query = "DELETE FROM FeeRecord WHERE FeeRecordID = %s"
cursor.execute(query, (fee_record_id,))
frame.destroy()
messagebox.showinfo("删除成功", "费用记录已成功删除")
remove_fee_record()
新增费用记录：
def add_fee_info():
add_window = tk.Toplevel()
add_window.title("新增费用记录")
tk.Label(add_window, text="房间ID:").grid(row=0, column=0)
room_id_entry = tk.Entry(add_window)
room_id_entry.grid(row=0, column=1)
tk.Label(add_window, text="水费:").grid(row=1, column=0)
water_fee_entry = tk.Entry(add_window)
water_fee_entry.grid(row=1, column=1)
tk.Label(add_window, text="电费:").grid(row=2, column=0)
electricity_fee_entry = tk.Entry(add_window)
electricity_fee_entry.grid(row=2, column=1)
tk.Label(add_window, text="IC卡ID:").grid(row=3, column=0)
iccard_id_entry = tk.Entry(add_window)
iccard_id_entry.grid(row=3, column=1)
tk.Label(add_window, text="支付日期:").grid(row=4, column=0)
payment_date_entry = tk.Entry(add_window)
payment_date_entry.grid(row=4, column=1)
tk.Label(add_window, text="支付状态:").grid(row=5, column=0)
payment_status_entry = tk.Entry(add_window)
payment_status_entry.grid(row=5, column=1)
def save_fee_info():
room_id = room_id_entry.get()
water_fee = water_fee_entry.get()
electricity_fee = electricity_fee_entry.get()
iccard_id = iccard_id_entry.get()
payment_date = payment_date_entry.get()
payment_status = payment_status_entry.get()
@run_in_thread
def insert_fee_info():
with connect_db() as cursor:
query = """
INSERT INTO FeeRecord (RoomID, WaterFee, ElectricityFee, ICCardID, PaymentDate, PaymentStatus)
VALUES (%s, %s, %s, %s, %s, %s)
"""
cursor.execute(query, (room_id, water_fee, electricity_fee, iccard_id, payment_date, payment_status))
messagebox.showinfo("成功", "费用记录已新增")
add_window.destroy()
insert_fee_info()
tk.Button(add_window, text="保存", command=save_fee_info).grid(row=6, column=0, columnspan=2)
8、IC卡管理功能
管理IC卡信息，包括IC卡的添加、修改和查询及其相关费用记录。
管理IC卡信息界面：
def manage_iccards():
iccards_window = tk.Toplevel()
iccards_window.title("IC卡管理")
tk.Button(iccards_window, text="查看所有IC卡", command=show_all_iccards).pack()
tk.Button(iccards_window, text="查询IC卡信息", command=query_iccard_info).pack()
tk.Button(iccards_window, text="新增IC卡", command=add_iccard_info).pack()
查看所有IC卡信息：
def show_all_iccards():
all_iccards_window = tk.Toplevel()
all_iccards_window.title("所有IC卡信息")
@run_in_thread
def load_iccards():
with connect_db() as cursor:
query = "SELECT ICCardID, CardNumber, UserID, Balance, IssueDate, LastRechargeDate FROM ICCard"
cursor.execute(query)
iccards = cursor.fetchall()
for iccard in iccards:
iccard_id, card_number, user_id, balance, issue_date, last_recharge_date = iccard
iccard_frame = tk.Frame(all_iccards_window)
iccard_frame.pack()
tk.Label(iccard_frame, text=f"卡号: {card_number}, 用户ID: {user_id}, 余额: {balance}, 发行日期: {issue_date}, 最后充值日期: {last_recharge_date}").pack()
tk.Button(iccard_frame, text="修改IC卡信息", command=lambda cid=iccard_id: edit_iccard_info(cid)).pack()
tk.Button(iccard_frame, text="查询费用记录", command=lambda cid=iccard_id: query_fee_records_by_iccard(cid)).pack()
load_iccards()
查询IC卡信息：
def query_iccard_info():
query_window = tk.Toplevel()
query_window.title("查询IC卡信息")
tk.Label(query_window, text="输入IC卡号或IC卡ID:").pack()
query_entry = tk.Entry(query_window)
query_entry.pack()
@run_in_thread
def search():
query_value = query_entry.get()
with connect_db() as cursor:
query = "SELECT ICCardID FROM ICCard WHERE CardNumber = %s OR ICCardID = %s"
cursor.execute(query, (query_value, query_value))
iccard = cursor.fetchone()
if iccard:
iccard_id = iccard[0]
query_window.destroy()
show_iccard_detail(iccard_id)
else:
messagebox.showerror("查询失败", "没有找到匹配的IC卡")
tk.Button(query_window, text="查询", command=search).pack()
查询IC卡信息：
def query_iccard_info():
query_window = tk.Toplevel()
query_window.title("查询IC卡信息")
tk.Label(query_window, text="输入IC卡号或IC卡ID:").pack()
query_entry = tk.Entry(query_window)
query_entry.pack()
@run_in_thread
def search():
query_value = query_entry.get()
with connect_db() as cursor:
query = "SELECT ICCardID FROM ICCard WHERE CardNumber = %s OR ICCardID = %s"
cursor.execute(query, (query_value, query_value))
iccard = cursor.fetchone()
if iccard:
iccard_id = iccard[0]
query_window.destroy()
show_iccard_detail(iccard_id)
else:
messagebox.showerror("查询失败", "没有找到匹配的IC卡")
tk.Button(query_window, text="查询", command=search).pack()
查看IC卡详细信息：
def show_iccard_detail(iccard_id):
detail_window = tk.Toplevel()
detail_window.title("IC卡详细信息")
@run_in_thread
def load_iccard_detail():
with connect_db() as cursor:
query = "SELECT CardNumber, UserID, Balance, IssueDate, LastRechargeDate FROM ICCard WHERE ICCardID = %s"
cursor.execute(query, (iccard_id,))
iccard_info = cursor.fetchone()
if iccard_info:
card_number, user_id, balance, issue_date, last_recharge_date = iccard_info
tk.Label(detail_window, text=f"卡号: {card_number}").pack()
tk.Label(detail_window, text=f"用户ID: {user_id}").pack()
tk.Label(detail_window, text=f"余额: {balance}").pack()
tk.Label(detail_window, text=f"发行日期: {issue_date}").pack()
tk.Label(detail_window, text=f"最后充值日期: {last_recharge_date}").pack()
tk.Button(detail_window, text="修改IC卡信息", command=lambda: edit_iccard_info(iccard_id)).pack()
tk.Button(detail_window, text="查询费用记录", command=lambda: query_fee_records_by_iccard(iccard_id)).pack()
load_iccard_detail()
修改IC卡信息：
def edit_iccard_info(iccard_id):
edit_window = tk.Toplevel()
edit_window.title("修改IC卡信息")
@run_in_thread
def load_iccard_info():
with connect_db() as cursor:
query = "SELECT CardNumber, UserID, Balance, IssueDate, LastRechargeDate FROM ICCard WHERE ICCardID = %s"
cursor.execute(query, (iccard_id,))
iccard_info = cursor.fetchone()
if iccard_info:
card_number, user_id, balance, issue_date, last_recharge_date = iccard_info
tk.Label(edit_window, text="卡号:").grid(row=0, column=0)
card_number_entry = tk.Entry(edit_window)
card_number_entry.insert(0, card_number)
card_number_entry.grid(row=0, column=1)
tk.Label(edit_window, text="用户ID:").grid(row=1, column=0)
user_id_entry = tk.Entry(edit_window)
user_id_entry.insert(0, user_id)
user_id_entry.grid(row=1, column=1)
tk.Label(edit_window, text="余额:").grid(row=2, column=0)
balance_entry = tk.Entry(edit_window)
balance_entry.insert(0, balance)
balance_entry.grid(row=2, column=1)
tk.Label(edit_window, text="发行日期:").grid(row=3, column=0)
issue_date_entry = tk.Entry(edit_window)
issue_date_entry.insert(0, issue_date)
issue_date_entry.grid(row=3, column=1)
tk.Label(edit_window, text="最后充值日期:").grid(row=4, column=0)
last_recharge_date_entry = tk.Entry(edit_window)
last_recharge_date_entry.insert(0, last_recharge_date)
last_recharge_date_entry.grid(row=4, column=1)
def save_iccard_info():
new_card_number = card_number_entry.get()
new_user_id = user_id_entry.get()
new_balance = balance_entry.get()
new_issue_date = issue_date_entry.get()
new_last_recharge_date = last_recharge_date_entry.get()
@run_in_thread
def update_iccard_info():
with connect_db() as cursor:
query = """
UPDATE ICCard
SET CardNumber = %s, UserID = %s, Balance = %s, IssueDate = %s, LastRechargeDate = %s
WHERE ICCardID = %s
"""
cursor.execute(query, (new_card_number, new_user_id, new_balance, new_issue_date, new_last_recharge_date, iccard_id))
messagebox.showinfo("成功", "IC卡信息已更新")
edit_window.destroy()
update_iccard_info()
tk.Button(edit_window, text="保存", command=save_iccard_info).grid(row=5, column=0, columnspan=2)
load_iccard_info()
查询IC卡的费用记录：
def query_fee_records_by_iccard(iccard_id):
records_window = tk.Toplevel()
records_window.title("IC卡费用记录")
@run_in_thread
def load_fee_records():
with connect_db() as cursor:
query = """
SELECT fr.FeeRecordID, fr.RoomID, fr.WaterFee, fr.ElectricityFee, fr.PaymentDate, fr.PaymentStatus
FROM FeeRecord fr
WHERE fr.ICCardID = %s
"""
cursor.execute(query, (iccard_id,))
records = cursor.fetchall()
if records:
for record in records:
fee_record_id, room_id, water_fee, electricity_fee, payment_date, payment_status = record
record_frame = tk.Frame(records_window)
record_frame.pack()
tk.Label(record_frame, text=f"房间ID: {room_id}, 水费: {water_fee}, 电费: {electricity_fee}, 支付日期: {payment_date}, 支付状态: {payment_status}").pack()
tk.Button(record_frame, text="修改费用记录", command=lambda frid=fee_record_id: edit_fee_info(frid)).pack()
else:
tk.Label(records_window, text="没有找到相关费用记录").pack()
load_fee_records()
新增IC卡信息：
def add_iccard_info():
add_window = tk.Toplevel()
add_window.title("新增IC卡信息")
tk.Label(add_window, text="卡号:").grid(row=0, column=0)
card_number_entry = tk.Entry(add_window)
card_number_entry.grid(row=0, column=1)
tk.Label(add_window, text="用户ID:").grid(row=1, column=0)
user_id_entry = tk.Entry(add_window)
user_id_entry.grid(row=1, column=1)
tk.Label(add_window, text="余额:").grid(row=2, column=0)
balance_entry = tk.Entry(add_window)
balance_entry.grid(row=2, column=1)
tk.Label(add_window, text="发行日期:").grid(row=3, column=0)
issue_date_entry = tk.Entry(add_window)
issue_date_entry.grid(row=3, column=1)
tk.Label(add_window, text="最后充值日期:").grid(row=4, column=0)
last_recharge_date_entry = tk.Entry(add_window)
last_recharge_date_entry.grid(row=4, column=1)
def save_iccard_info():
card_number = card_number_entry.get()
user_id = user_id_entry.get()
balance = balance_entry.get()
issue_date = issue_date_entry.get()
last_recharge_date = last_recharge_date_entry.get()
@run_in_thread
def insert_iccard_info():
with connect_db() as cursor:
query = """
INSERT INTO ICCard (CardNumber, UserID, Balance, IssueDate, LastRechargeDate)
VALUES (%s, %s, %s, %s, %s)
"""
cursor.execute(query, (card_number, user_id, balance, issue_date, last_recharge_date))
messagebox.showinfo("成功", "IC卡信息已新增")
add_window.destroy()
insert_iccard_info()
tk.Button(add_window, text="保存", command=save_iccard_info).grid(row=5, column=0, columnspan=2)
六、主要功能测试
（一）测试环境
1、操作系统：Windows 11
2、编程语言：Python 3.12.3
3、数据库：MySQL 8.0
4、开发工具：VS Code、SQLyog
（二）程序运行界面（主要）
1、管理员界面
2、用户界面
（三）测试结果（示例）
1、用户登录功能测试
测试目标：验证普通用户和管理员的登录功能。
测试用例：
| 测试编号 | 测试项 | 输入数据 | 预期输出 | 实际输出 | 测试结果 |
|---|---|---|---|---|---|
| 1 | 普通用户登录 | 用户名：user1 密码：password1 | 登录成功，显示用户信息界面 | √ | √ |
| 2 | 普通用户登录 | 用户名：user1 密码：wrongpass | 登录失败，提示“用户名或密码错误” | √ | √ |
| 3 | 管理员登录 | 用户名：admin 密码：adminpass | 登录成功，显示管理员界面 | √ | √ |
| 4 | 管理员登录 | 用户名：admin 密码：wrongpass | 登录失败，提示“用户名或密码错误” | √ | √ |
2、用户信息管理功能测试
测试目标：验证用户信息的查询和更新功能。
测试用例：
| 测试编号 | 测试项 | 输入数据 | 预期输出 | 实际输出 | 测试结果 |
|---|---|---|---|---|---|
| 1 | 查看用户信息 | 用户ID：1 | 显示用户ID为1的用户信息 | √ | √ |
| 2 | 更新用户联系方式 | 用户ID：1 | 更新成功，显示新的联系方式 | √ | √ |
| 3 | 更新用户地址 | 用户ID：1 | 更新成功，显示新的地址 | √ | √ |
3、房间管理功能测试
测试目标：验证房间信息的添加、查询、修改和删除功能。
测试用例：
| 测试编号 | 测试项 | 输入数据 | 预期输出 | 实际输出 | 测试结果 |
|---|---|---|---|---|---|
| 1 | 添加房间信息 | 房间号：101 楼层：1 房间类型：公寓 | 添加成功，数据库中存在新房间信息 | √ | √ |
| 2 | 查询房间信息 | 房间号：101 | 显示房间号为101的房间信息 | √ | √ |
| 3 | 修改房间信息 | 房间号：101 | 修改成功，显示更新后的房间信息 | √ | √ |
| 4 | 删除房间信息 | 房间号：101 | 删除成功，数据库中不存在房间号为101的房间信息 | √ | √ |
4、水电表管理功能测试
测试目标：验证水表和电表信息的添加、查询、修改和删除功能。
测试用例：
| 测试编号 | 测试项 | 输入数据 | 预期输出 | 实际输出 | 测试结果 |
|---|---|---|---|---|---|
| 1 | 添加水表信息 | 表号：WM101 房间号：101 | 添加成功，数据库中存在新水表信息 | √ | √ |
| 2 | 查询水表信息 | 表号：WM101 | 显示表号为WM101的水表信息 | √ | √ |
| 3 | 修改水表信息 | 表号：WM101 | 修改成功，显示更新后的水表信息 | √ | √ |
| 4 | 删除水表信息 | 表号：WM101 | 删除成功，数据库中不存在表号为WM101的水表信息 | √ | √ |
| 5 | 添加电表信息 | 表号：EM101 房间号：101 | 添加成功，数据库中存在新电表信息 | √ | √ |
| 6 | 查询电表信息 | 表号：EM101 | 显示表号为EM101的电表信息 | √ | √ |
| 7 | 修改电表信息 | 表号：EM101 | 修改成功，显示更新后的电表信息 | √ | √ |
| 8 | 删除电表信息 | 表号：EM101 | 删除成功，数据库中不存在表号为EM101的电表信息 | √ | √ |
5、费用管理功能测试
测试目标：验证费用记录的添加、查询和修改功能。
测试用例：
| 测试编号 | 测试项 | 输入数据 | 预期输出 | 实际输出 | 测试结果 |
|---|---|---|---|---|---|
| 1 | 添加费用记录 | 房间号：101 水费：100 电费：200 | 添加成功，数据库中存在新的费用记录 | √ | √ |
| 2 | 查询费用记录 | 房间号：101 | 显示房间号为101的费用记录 | √ | √ |
| 3 | 修改费用记录 | 房间号：101 | 修改成功，显示更新后的费用记录 | √ | √ |
6、IC卡管理功能测试
测试目标：验证IC卡信息的添加、查询、修改和删除功能。
测试用例：
| 测试编号 | 测试项 | 输入数据 | 预期输出 | 实际输出 | 测试结果 |
|---|---|---|---|---|---|
| 1 | 添加IC卡信息 | 卡号：IC101 用户ID：1 | 添加成功，数据库中存在新的IC卡信息 | √ | √ |
| 2 | 查询IC卡信息 | 卡号：IC101 | 显示卡号为IC101的IC卡信息 | √ | √ |
| 3 | 修改IC卡信息 | 卡号：IC101 | 修改成功，显示更新后的IC卡信息 | √ | √ |
| 4 | 删除IC卡信息 | 卡号：IC101 | 删除成功，数据库中不存在卡号为IC101的IC卡信息 | √ | √ |
七、总结
虽然本系统已经实现了物业管理的基本功能，但仍有一些改进和扩展的空间，可以包括：
1、增加更多智能化和自动化功能，如自动抄表、费用自动计算和提醒等。
2、优化数据库查询和前端界面的性能，提高系统的响应速度和处理效率。
3、加强系统的安全性措施，保护用户数据的隐私和安全。
附件1：
import tkinter as tk
from tkinter import messagebox
import mysql.connector
from mysql.connector import Error
from contextlib import contextmanager
import threading
@contextmanager
def connect_db():
try:
cnx = mysql.connector.connect(
host='localhost',
user='root',
password='123',
database='propertymanagement'
)
cursor = cnx.cursor()
yield cursor
cnx.commit()
except Error as e:
messagebox.showerror("数据库错误", f"错误: {e}")
finally:
cursor.close()
cnx.close()
def run_in_thread(func):
def wrapper(*args, **kwargs):
threading.Thread(target=func, args=args, kwargs=kwargs).start()
return wrapper
# 用户登录页面
def user_login():
login_window = tk.Toplevel()
login_window.title("用户登录")
tk.Label(login_window, text="用户名:").pack()
username_entry = tk.Entry(login_window)
username_entry.pack()
tk.Label(login_window, text="密码:").pack()
password_entry = tk.Entry(login_window, show="*")
password_entry.pack()
@run_in_thread
def login():
username = username_entry.get()
password = password_entry.get()
with connect_db() as cursor:
query = "SELECT UserID FROM User WHERE Username = %s AND Password = %s AND UserType = '普通用户'"
cursor.execute(query, (username, password))
user = cursor.fetchone()
if user:
user_id = user[0]
login_window.destroy()
user_interface(user_id)
else:
messagebox.showerror("登录失败", "用户名或密码错误")
tk.Button(login_window, text="登录", command=login).pack()
# 用户信息页面
def user_interface(user_id):
user_window = tk.Toplevel()
user_window.title("用户信息")
@run_in_thread
def load_user_info():
with connect_db() as cursor:
query = "SELECT Username, ContactInfo, Address FROM User WHERE UserID = %s"
cursor.execute(query, (user_id,))
user_info = cursor.fetchone()
if user_info:
username, contact_info, address = user_info
tk.Label(user_window, text=f"用户名: {username}").pack()
tk.Label(user_window, text=f"联系方式: {contact_info}").pack()
tk.Label(user_window, text=f"地址: {address}").pack()
query = "SELECT RoomNumber FROM Room WHERE UserID = %s"
cursor.execute(query, (user_id,))
rooms = cursor.fetchall()
room_numbers = [room[0] for room in rooms]
tk.Label(user_window, text="房间信息:").pack()
for room_number in room_numbers:
tk.Label(user_window, text=room_number).pack()
query = "SELECT ICCardID, CardNumber, Balance FROM ICCard WHERE UserID = %s"
cursor.execute(query, (user_id,))
iccards = cursor.fetchall()
tk.Label(user_window, text="IC卡信息:").pack()
for iccard_id, card_number, balance in iccards:
card_frame = tk.Frame(user_window)
card_frame.pack()
tk.Label(card_frame, text=f"卡号: {card_number}, 余额: {balance}").pack()
tk.Button(card_frame, text="查询消费记录", command=lambda cid=iccard_id: query_fee_records_by_iccard(cid, False)).pack()
load_user_info()
tk.Button(user_window, text="查询房间费用", command=lambda: query_user_fees(user_id)).pack()
# 查询房间费用
def query_user_fees(user_id):
user_fees = tk.Toplevel()
user_fees.title("房间费用查询")
tk.Label(user_fees, text="选择房间号:").pack()
@run_in_thread
def load_rooms():
with connect_db() as cursor:
query = "SELECT RoomID, RoomNumber FROM Room WHERE UserID = %s"
cursor.execute(query, (user_id,))
rooms = cursor.fetchall()
if not rooms:
tk.Label(user_fees, text="没有找到房间").pack()
return
room_var = tk.StringVar()
room_var.set(rooms[0][1] if rooms else "")
room_menu = tk.OptionMenu(user_fees, room_var, *[room[1] for room in rooms])
room_menu.pack()
def on_room_select():
room_number = room_var.get()
room_id = [room[0] for room in rooms if room[1] == room_number][0]
show_fees(room_id, False)
tk.Button(user_fees, text="查询费用", command=on_room_select).pack()
load_rooms()
# 显示房间费用信息
def show_fees(room_id, is_admin):
fees_window = tk.Toplevel()
fees_window.title("房间费用信息")
@run_in_thread
def load_fees():
with connect_db() as cursor:
query = """
SELECT wm.WaterMeterID, wm.CurrentReading, wm.LastReadingDate, em.ElectricityMeterID, em.CurrentReading, em.LastReadingDate, fr.WaterFee, fr.ElectricityFee
FROM WaterMeter wm
JOIN ElectricityMeter em ON wm.RoomID = em.RoomID
JOIN FeeRecord fr ON wm.RoomID = fr.RoomID
WHERE wm.RoomID = %s
"""
cursor.execute(query, (room_id,))
result = cursor.fetchone()
if result:
water_meter_id, wm_reading, wm_date, electricity_meter_id, em_reading, em_date, water_fee, electricity_fee = result
tk.Label(fees_window, text=f"水表读数: {wm_reading}, 最后读取日期: {wm_date}").pack()
tk.Label(fees_window, text=f"电表读数: {em_reading}, 最后读取日期: {em_date}").pack()
tk.Label(fees_window, text=f"水费: {water_fee}").pack()
tk.Label(fees_window, text=f"电费: {electricity_fee}").pack()
if is_admin:
tk.Button(fees_window, text="修改水电表信息", command=lambda: edit_meter_info(water_meter_id, electricity_meter_id)).pack()
tk.Button(fees_window, text="修改费用信息", command=lambda: edit_fee_info(room_id)).pack()
else:
tk.Label(fees_window, text="没有找到相关费用信息").pack()
load_fees()
# 修改水电表信息
def edit_meter_info(water_meter_id, electricity_meter_id):
edit_window = tk.Toplevel()
edit_window.title("修改水电表信息")
@run_in_thread
def load_meter_info():
with connect_db() as cursor:
query = "SELECT WaterMeterNumber, CurrentReading, LastReadingDate FROM WaterMeter WHERE WaterMeterID = %s"
cursor.execute(query, (water_meter_id,))
water_meter_info = cursor.fetchone()
query = "SELECT ElectricityMeterNumber, CurrentReading, LastReadingDate FROM ElectricityMeter WHERE ElectricityMeterID = %s"
cursor.execute(query, (electricity_meter_id,))
electricity_meter_info = cursor.fetchone()
if water_meter_info and electricity_meter_info:
wm_number, wm_reading, wm_date = water_meter_info
em_number, em_reading, em_date = electricity_meter_info
tk.Label(edit_window, text="水表号:").grid(row=0, column=0)
wm_number_entry = tk.Entry(edit_window)
wm_number_entry.insert(0, wm_number)
wm_number_entry.grid(row=0, column=1)
tk.Label(edit_window, text="水表读数:").grid(row=1, column=0)
wm_reading_entry = tk.Entry(edit_window)
wm_reading_entry.insert(0, wm_reading)
wm_reading_entry.grid(row=1, column=1)
tk.Label(edit_window, text="最后读数日期:").grid(row=2, column=0)
wm_date_entry = tk.Entry(edit_window)
wm_date_entry.insert(0, wm_date)
wm_date_entry.grid(row=2, column=1)
tk.Label(edit_window, text="电表号:").grid(row=3, column=0)
em_number_entry = tk.Entry(edit_window)
em_number_entry.insert(0, em_number)
em_number_entry.grid(row=3, column=1)
tk.Label(edit_window, text="电表读数:").grid(row=4, column=0)
em_reading_entry = tk.Entry(edit_window)
em_reading_entry.insert(0, em_reading)
em_reading_entry.grid(row=4, column=1)
tk.Label(edit_window, text="最后读数日期:").grid(row=5, column=0)
em_date_entry = tk.Entry(edit_window)
em_date_entry.insert(0, em_date)
em_date_entry.grid(row=5, column=1)
def save_meter_info():
new_wm_number = wm_number_entry.get()
new_wm_reading = wm_reading_entry.get()
new_wm_date = wm_date_entry.get()
new_em_number = em_number_entry.get()
new_em_reading = em_reading_entry.get()
new_em_date = em_date_entry.get()
@run_in_thread
def update_meter_info():
with connect_db() as cursor:
query = "UPDATE WaterMeter SET WaterMeterNumber = %s, CurrentReading = %s, LastReadingDate = %s WHERE WaterMeterID = %s"
cursor.execute(query, (new_wm_number, new_wm_reading, new_wm_date, water_meter_id))
query = "UPDATE ElectricityMeter SET ElectricityMeterNumber = %s, CurrentReading = %s, LastReadingDate = %s WHERE ElectricityMeterID = %s"
cursor.execute(query, (new_em_number, new_em_reading, new_em_date, electricity_meter_id))
messagebox.showinfo("成功", "水电表信息已更新")
edit_window.destroy()
update_meter_info()
tk.Button(edit_window, text="保存", command=save_meter_info).grid(row=6, column=0, columnspan=2)
load_meter_info()
# 修改费用信息
def edit_fee_info(room_id):
edit_window = tk.Toplevel()
edit_window.title("修改费用信息")
@run_in_thread
def load_fee_info():
with connect_db() as cursor:
query = "SELECT WaterFee, ElectricityFee FROM FeeRecord WHERE RoomID = %s"
cursor.execute(query, (room_id,))
fee_info = cursor.fetchone()
if fee_info:
water_fee, electricity_fee = fee_info
tk.Label(edit_window, text="水费:").grid(row=0, column=0)
water_fee_entry = tk.Entry(edit_window)
water_fee_entry.insert(0, water_fee)
water_fee_entry.grid(row=0, column=1)
tk.Label(edit_window, text="电费:").grid(row=1, column=0)
electricity_fee_entry = tk.Entry(edit_window)
electricity_fee_entry.insert(0, electricity_fee)
electricity_fee_entry.grid(row=1, column=1)
def save_fee_info():
new_water_fee = water_fee_entry.get()
new_electricity_fee = electricity_fee_entry.get()
@run_in_thread
def update_fee_info():
with connect_db() as cursor:
query = "UPDATE FeeRecord SET WaterFee = %s, ElectricityFee = %s WHERE RoomID = %s"
cursor.execute(query, (new_water_fee, new_electricity_fee, room_id))
messagebox.showinfo("成功", "费用信息已更新")
edit_window.destroy()
update_fee_info()
tk.Button(edit_window, text="保存", command=save_fee_info).grid(row=2, column=0, columnspan=2)
load_fee_info()
# 管理员登录页面
def admin_login():
login_window = tk.Toplevel()
login_window.title("管理员登录")
tk.Label(login_window, text="用户名:").pack()
username_entry = tk.Entry(login_window)
username_entry.pack()
tk.Label(login_window, text="密码:").pack()
password_entry = tk.Entry(login_window, show="*")
password_entry.pack()
@run_in_thread
def login():
username = username_entry.get()
password = password_entry.get()
with connect_db() as cursor:
query = "SELECT UserID FROM User WHERE Username = %s AND Password = %s AND UserType = '管理员'"
cursor.execute(query, (username, password))
user = cursor.fetchone()
if user:
login_window.destroy()
admin_interface()
else:
messagebox.showerror("登录失败", "用户名或密码错误")
tk.Button(login_window, text="登录", command=login).pack()
# 管理员主界面
def admin_interface():
admin_window = tk.Toplevel()
admin_window.title("管理员界面")
tk.Button(admin_window, text="查看所有用户信息", command=show_all_users).pack()
tk.Button(admin_window, text="查询用户详细信息", command=query_user_info).pack()
tk.Button(admin_window, text="新增信息", command=add_info).pack()
# 查看所有用户信息
def show_all_users():
users_window = tk.Toplevel()
users_window.title("所有用户信息")
@run_in_thread
def load_users():
with connect_db() as cursor:
query = "SELECT UserID, Username, ContactInfo, Address FROM User WHERE UserType = '普通用户'"
cursor.execute(query)
users = cursor.fetchall()
for user in users:
user_id, username, contact_info, address = user
user_frame = tk.Frame(users_window)
user_frame.pack()
tk.Label(user_frame, text=f"用户名: {username}, 联系方式: {contact_info}, 地址: {address}").pack()
tk.Button(user_frame, text="详细信息", command=lambda uid=user_id: show_user_detail(uid)).pack()
tk.Button(user_frame, text="删除用户", command=lambda uid=user_id: delete_user(uid, user_frame)).pack()
load_users()
# 查询用户信息（通过用户名或用户ID）
def query_user_info():
query_window = tk.Toplevel()
query_window.title("查询用户信息")
tk.Label(query_window, text="输入用户名或用户ID:").pack()
query_entry = tk.Entry(query_window)
query_entry.pack()
@run_in_thread
def search():
query_value = query_entry.get()
with connect_db() as cursor:
query = "SELECT UserID FROM User WHERE Username = %s OR UserID = %s"
cursor.execute(query, (query_value, query_value))
user = cursor.fetchone()
if user:
user_id = user[0]
query_window.destroy()
show_user_detail(user_id)
else:
messagebox.showerror("查询失败", "没有找到匹配的用户")
tk.Button(query_window, text="查询", command=search).pack()
# 查看用户详细信息
def show_user_detail(user_id):
detail_window = tk.Toplevel()
detail_window.title("用户详细信息")
@run_in_thread
def load_user_detail():
with connect_db() as cursor:
query = "SELECT Username, ContactInfo, Address FROM User WHERE UserID = %s"
cursor.execute(query, (user_id,))
user_info = cursor.fetchone()
if user_info:
username, contact_info, address = user_info
tk.Label(detail_window, text=f"用户名: {username}").pack()
tk.Label(detail_window, text=f"联系方式: {contact_info}").pack()
tk.Label(detail_window, text=f"地址: {address}").pack()
tk.Button(detail_window, text="修改用户信息", command=lambda: edit_user_info(user_id)).pack()
query = "SELECT RoomID, RoomNumber FROM Room WHERE UserID = %s"
cursor.execute(query, (user_id,))
rooms = cursor.fetchall()
tk.Label(detail_window, text="房间信息:").pack()
for room_id, room_number in rooms:
room_frame = tk.Frame(detail_window)
room_frame.pack()
tk.Label(room_frame, text=room_number).pack()
tk.Button(room_frame, text="查询费用", command=lambda rid=room_id: show_fees(rid, True)).pack()
tk.Button(room_frame, text="修改房间信息", command=lambda rid=room_id: edit_room_info(rid)).pack()
query = "SELECT ICCardID, CardNumber, Balance FROM ICCard WHERE UserID = %s"
cursor.execute(query, (user_id,))
iccards = cursor.fetchall()
tk.Label(detail_window, text="IC卡信息:").pack()
for iccard_id, card_number, balance in iccards:
card_frame = tk.Frame(detail_window)
card_frame.pack()
tk.Label(card_frame, text=f"卡号: {card_number}, 余额: {balance}").pack()
tk.Button(card_frame, text="修改IC卡信息", command=lambda cid=iccard_id: edit_iccard_info(cid)).pack()
tk.Button(card_frame, text="查询消费记录", command=lambda cid=iccard_id: query_fee_records_by_iccard(cid, True)).pack()
load_user_detail()
# 修改用户信息
def edit_user_info(user_id):
edit_window = tk.Toplevel()
edit_window.title("修改用户信息")
@run_in_thread
def load_user_info():
with connect_db() as cursor:
query = "SELECT Username, ContactInfo, Address FROM User WHERE UserID = %s"
cursor.execute(query, (user_id,))
user_info = cursor.fetchone()
if user_info:
username, contact_info, address = user_info
tk.Label(edit_window, text="用户名:").grid(row=0, column=0)
username_entry = tk.Entry(edit_window)
username_entry.insert(0, username)
username_entry.grid(row=0, column=1)
tk.Label(edit_window, text="联系方式:").grid(row=1, column=0)
contact_entry = tk.Entry(edit_window)
contact_entry.insert(0, contact_info)
contact_entry.grid(row=1, column=1)
tk.Label(edit_window, text="地址:").grid(row=2, column=0)
address_entry = tk.Entry(edit_window)
address_entry.insert(0, address)
address_entry.grid(row=2, column=1)
def save_user_info():
new_username = username_entry.get()
new_contact = contact_entry.get()
new_address = address_entry.get()
@run_in_thread
def update_user_info():
with connect_db() as cursor:
query = "UPDATE User SET Username = %s, ContactInfo = %s, Address = %s WHERE UserID = %s"
cursor.execute(query, (new_username, new_contact, new_address, user_id))
messagebox.showinfo("成功", "用户信息已更新")
edit_window.destroy()
update_user_info()
tk.Button(edit_window, text="保存", command=save_user_info).grid(row=3, column=0, columnspan=2)
load_user_info()
# 修改房间信息
def edit_room_info(room_id):
edit_window = tk.Toplevel()
edit_window.title("修改房间信息")
@run_in_thread
def load_room_info():
with connect_db() as cursor:
query = "SELECT RoomNumber, Floor, RoomType FROM Room WHERE RoomID = %s"
cursor.execute(query, (room_id,))
room_info = cursor.fetchone()
if room_info:
room_number, floor, room_type = room_info
tk.Label(edit_window, text="房间号:").grid(row=0, column=0)
room_number_entry = tk.Entry(edit_window)
room_number_entry.insert(0, room_number)
room_number_entry.grid(row=0, column=1)
tk.Label(edit_window, text="楼层:").grid(row=1, column=0)
floor_entry = tk.Entry(edit_window)
floor_entry.insert(0, floor)
floor_entry.grid(row=1, column=1)
tk.Label(edit_window, text="房间类型:").grid(row=2, column=0)
room_type_entry = tk.Entry(edit_window)
room_type_entry.insert(0, room_type)
room_type_entry.grid(row=2, column=1)
def save_room_info():
new_room_number = room_number_entry.get()
new_floor = floor_entry.get()
new_room_type = room_type_entry.get()
@run_in_thread
def update_room_info():
with connect_db() as cursor:
query = "UPDATE Room SET RoomNumber = %s, Floor = %s, RoomType = %s WHERE RoomID = %s"
cursor.execute(query, (new_room_number, new_floor, new_room_type, room_id))
messagebox.showinfo("成功", "房间信息已更新")
edit_window.destroy()
update_room_info()
tk.Button(edit_window, text="保存", command=save_room_info).grid(row=3, column=0, columnspan=2)
load_room_info()
# 修改IC卡信息
def edit_iccard_info(iccard_id):
edit_window = tk.Toplevel()
edit_window.title("修改IC卡信息")
@run_in_thread
def load_iccard_info():
with connect_db() as cursor:
query = "SELECT CardNumber, Balance FROM ICCard WHERE ICCardID = %s"
cursor.execute(query, (iccard_id,))
iccard_info = cursor.fetchone()
if iccard_info:
card_number, balance = iccard_info
tk.Label(edit_window, text="卡号:").grid(row=0, column=0)
card_number_entry = tk.Entry(edit_window)
card_number_entry.insert(0, card_number)
card_number_entry.grid(row=0, column=1)
tk.Label(edit_window, text="余额:").grid(row=1, column=0)
balance_entry = tk.Entry(edit_window)
balance_entry.insert(0, balance)
balance_entry.grid(row=1, column=1)
def save_iccard_info():
new_card_number = card_number_entry.get()
new_balance = balance_entry.get()
@run_in_thread
def update_iccard_info():
with connect_db() as cursor:
query = "UPDATE ICCard SET CardNumber = %s, Balance = %s WHERE ICCardID = %s"
cursor.execute(query, (new_card_number, new_balance, iccard_id))
messagebox.showinfo("成功", "IC卡信息已更新")
edit_window.destroy()
update_iccard_info()
tk.Button(edit_window, text="保存", command=save_iccard_info).grid(row=2, column=0, columnspan=2)
load_iccard_info()
# 查询IC卡的消费记录
def query_fee_records_by_iccard(iccard_id, is_admin):
records_window = tk.Toplevel()
records_window.title("IC卡消费记录")
@run_in_thread
def load_fee_records():
with connect_db() as cursor:
query = """
SELECT fr.FeeRecordID, fr.RoomID, fr.WaterFee, fr.ElectricityFee, fr.PaymentDate, fr.PaymentStatus
FROM FeeRecord fr
WHERE fr.ICCardID = %s
"""
cursor.execute(query, (iccard_id,))
records = cursor.fetchall()
if records:
for record in records:
fee_record_id, room_id, water_fee, electricity_fee, payment_date, payment_status = record
record_frame = tk.Frame(records_window)
record_frame.pack()
tk.Label(record_frame, text=f"房间ID: {room_id}, 水费: {water_fee}, 电费: {electricity_fee}, 支付日期: {payment_date}, 支付状态: {payment_status}").pack()
if is_admin:
tk.Button(record_frame, text="修改消费记录", command=lambda frid=fee_record_id: edit_fee_record(frid)).pack()
else:
tk.Label(records_window, text="没有找到相关消费记录").pack()
load_fee_records()
# 修改消费记录
def edit_fee_record(fee_record_id):
edit_window = tk.Toplevel()
edit_window.title("修改消费记录")
@run_in_thread
def load_fee_record():
with connect_db() as cursor:
query = "SELECT RoomID, WaterFee, ElectricityFee, PaymentDate, PaymentStatus FROM FeeRecord WHERE FeeRecordID = %s"
cursor.execute(query, (fee_record_id,))
fee_record = cursor.fetchone()
if fee_record:
room_id, water_fee, electricity_fee, payment_date, payment_status = fee_record
tk.Label(edit_window, text="房间ID:").grid(row=0, column=0)
room_id_entry = tk.Entry(edit_window)
room_id_entry.insert(0, room_id)
room_id_entry.grid(row=0, column=1)
tk.Label(edit_window, text="水费:").grid(row=1, column=0)
water_fee_entry = tk.Entry(edit_window)
water_fee_entry.insert(0, water_fee)
water_fee_entry.grid(row=1, column=1)
tk.Label(edit_window, text="电费:").grid(row=2, column=0)
electricity_fee_entry = tk.Entry(edit_window)
electricity_fee_entry.insert(0, electricity_fee)
electricity_fee_entry.grid(row=2, column=1)
tk.Label(edit_window, text="支付日期:").grid(row=3, column=0)
payment_date_entry = tk.Entry(edit_window)
payment_date_entry.insert(0, payment_date)
payment_date_entry.grid(row=3, column=1)
tk.Label(edit_window, text="支付状态:").grid(row=4, column=0)
payment_status_entry = tk.Entry(edit_window)
payment_status_entry.insert(0, payment_status)
payment_status_entry.grid(row=4, column=1)
def save_fee_record():
new_room_id = room_id_entry.get()
new_water_fee = water_fee_entry.get()
new_electricity_fee = electricity_fee_entry.get()
new_payment_date = payment_date_entry.get()
new_payment_status = payment_status_entry.get()
@run_in_thread
def update_fee_record():
with connect_db() as cursor:
query = "UPDATE FeeRecord SET RoomID = %s, WaterFee = %s, ElectricityFee = %s, PaymentDate = %s, PaymentStatus = %s WHERE FeeRecordID = %s"
cursor.execute(query, (new_room_id, new_water_fee, new_electricity_fee, new_payment_date, new_payment_status, fee_record_id))
messagebox.showinfo("成功", "消费记录已更新")
edit_window.destroy()
update_fee_record()
tk.Button(edit_window, text="保存", command=save_fee_record).grid(row=5, column=0, columnspan=2)
load_fee_record()
# 删除用户
def delete_user(user_id, frame):
if messagebox.askyesno("确认删除", "你确定要删除这个用户吗？"):
@run_in_thread
def remove_user():
with connect_db() as cursor:
query = "DELETE FROM User WHERE UserID = %s"
cursor.execute(query, (user_id,))
frame.destroy()
messagebox.showinfo("删除成功", "用户已成功删除")
remove_user()
# 新增信息
def add_info():
add_window = tk.Toplevel()
add_window.title("新增信息")
def add_user():
user_info_window = tk.Toplevel(add_window)
user_info_window.title("新增用户")
tk.Label(user_info_window, text="用户名:").grid(row=0, column=0)
username_entry = tk.Entry(user_info_window)
username_entry.grid(row=0, column=1)
tk.Label(user_info_window, text="密码:").grid(row=1, column=0)
password_entry = tk.Entry(user_info_window, show="*")
password_entry.grid(row=1, column=1)
tk.Label(user_info_window, text="联系方式:").grid(row=2, column=0)
contact_entry = tk.Entry(user_info_window)
contact_entry.grid(row=2, column=1)
tk.Label(user_info_window, text="地址:").grid(row=3, column=0)
address_entry = tk.Entry(user_info_window)
address_entry.grid(row=3, column=1)
def save_user():
username = username_entry.get()
password = password_entry.get()
contact = contact_entry.get()
address = address_entry.get()
@run_in_thread
def insert_user():
with connect_db() as cursor:
query = "INSERT INTO User (Username, Password, ContactInfo, Address, UserType) VALUES (%s, %s, %s, %s, '普通用户')"
cursor.execute(query, (username, password, contact, address))
messagebox.showinfo("成功", "用户已新增")
user_info_window.destroy()
insert_user()
tk.Button(user_info_window, text="保存", command=save_user).grid(row=4, column=0, columnspan=2)
tk.Button(add_window, text="新增用户", command=add_user).pack()
def add_room():
room_info_window = tk.Toplevel(add_window)
room_info_window.title("新增房间")
tk.Label(room_info_window, text="房间号:").grid(row=0, column=0)
room_number_entry = tk.Entry(room_info_window)
room_number_entry.grid(row=0, column=1)
tk.Label(room_info_window, text="楼层:").grid(row=1, column=0)
floor_entry = tk.Entry(room_info_window)
floor_entry.grid(row=1, column=1)
tk.Label(room_info_window, text="房间类型:").grid(row=2, column=0)
room_type_entry = tk.Entry(room_info_window)
room_type_entry.grid(row=2, column=1)
tk.Label(room_info_window, text="用户ID:").grid(row=3, column=0)
user_id_entry = tk.Entry(room_info_window)
user_id_entry.grid(row=3, column=1)
def save_room():
room_number = room_number_entry.get()
floor = floor_entry.get()
room_type = room_type_entry.get()
user_id = user_id_entry.get()
@run_in_thread
def insert_room():
with connect_db() as cursor:
query = "INSERT INTO Room (RoomNumber, Floor, RoomType, UserID) VALUES (%s, %s, %s, %s)"
cursor.execute(query, (room_number, floor, room_type, user_id))
messagebox.showinfo("成功", "房间已新增")
room_info_window.destroy()
insert_room()
tk.Button(room_info_window, text="保存", command=save_room).grid(row=4, column=0, columnspan=2)
tk.Button(add_window, text="新增房间", command=add_room).pack()
def add_meter():
meter_info_window = tk.Toplevel(add_window)
meter_info_window.title("新增水电表")
tk.Label(meter_info_window, text="水电表类型:").grid(row=0, column=0)
meter_type_var = tk.StringVar(value="水表")
tk.OptionMenu(meter_info_window, meter_type_var, "水表", "电表").grid(row=0, column=1)
tk.Label(meter_info_window, text="表号:").grid(row=1, column=0)
meter_number_entry = tk.Entry(meter_info_window)
meter_number_entry.grid(row=1, column=1)
tk.Label(meter_info_window, text="房间ID:").grid(row=2, column=0)
room_id_entry = tk.Entry(meter_info_window)
room_id_entry.grid(row=2, column=1)
tk.Label(meter_info_window, text="当前读数:").grid(row=3, column=0)
current_reading_entry = tk.Entry(meter_info_window)
current_reading_entry.grid(row=3, column=1)
tk.Label(meter_info_window, text="最后读数日期:").grid(row=4, column=0)
last_reading_date_entry = tk.Entry(meter_info_window)
last_reading_date_entry.grid(row=4, column=1)
def save_meter():
meter_type = meter_type_var.get()
meter_number = meter_number_entry.get()
room_id = room_id_entry.get()
current_reading = current_reading_entry.get()
last_reading_date = last_reading_date_entry.get()
@run_in_thread
def insert_meter():
with connect_db() as cursor:
if meter_type == "水表":
query = "INSERT INTO WaterMeter (WaterMeterNumber, RoomID, CurrentReading, LastReadingDate) VALUES (%s, %s, %s, %s)"
else:
query = "INSERT INTO ElectricityMeter (ElectricityMeterNumber, RoomID, CurrentReading, LastReadingDate) VALUES (%s, %s, %s, %s)"
cursor.execute(query, (meter_number, room_id, current_reading, last_reading_date))
messagebox.showinfo("成功", f"{meter_type}已新增")
meter_info_window.destroy()
insert_meter()
tk.Button(meter_info_window, text="保存", command=save_meter).grid(row=5, column=0, columnspan=2)
tk.Button(add_window, text="新增水电表", command=add_meter).pack()
def add_fee_record():
fee_info_window = tk.Toplevel(add_window)
fee_info_window.title("新增费用记录")
tk.Label(fee_info_window, text="房间ID:").grid(row=0, column=0)
room_id_entry = tk.Entry(fee_info_window)
room_id_entry.grid(row=0, column=1)
tk.Label(fee_info_window, text="水费:").grid(row=1, column=0)
water_fee_entry = tk.Entry(fee_info_window)
water_fee_entry.grid(row=1, column=1)
tk.Label(fee_info_window, text="电费:").grid(row=2, column=0)
electricity_fee_entry = tk.Entry(fee_info_window)
electricity_fee_entry.grid(row=2, column=1)
tk.Label(fee_info_window, text="IC卡ID:").grid(row=3, column=0)
iccard_id_entry = tk.Entry(fee_info_window)
iccard_id_entry.grid(row=3, column=1)
tk.Label(fee_info_window, text="支付日期:").grid(row=4, column=0)
payment_date_entry = tk.Entry(fee_info_window)
payment_date_entry.grid(row=4, column=1)
tk.Label(fee_info_window, text="支付状态:").grid(row=5, column=0)
payment_status_entry = tk.Entry(fee_info_window)
payment_status_entry.grid(row=5, column=1)
def save_fee_record():
room_id = room_id_entry.get()
water_fee = water_fee_entry.get()
electricity_fee = electricity_fee_entry.get()
iccard_id = iccard_id_entry.get()
payment_date = payment_date_entry.get()
payment_status = payment_status_entry.get()
@run_in_thread
def insert_fee_record():
with connect_db() as cursor:
query = "INSERT INTO FeeRecord (RoomID, WaterFee, ElectricityFee, ICCardID, PaymentDate, PaymentStatus) VALUES (%s, %s, %s, %s, %s, %s)"
cursor.execute(query, (room_id, water_fee, electricity_fee, iccard_id, payment_date, payment_status))
messagebox.showinfo("成功", "费用记录已新增")
fee_info_window.destroy()
insert_fee_record()
tk.Button(fee_info_window, text="保存", command=save_fee_record).grid(row=6, column=0, columnspan=2)
tk.Button(add_window, text="新增费用记录", command=add_fee_record).pack()
def add_iccard():
iccard_info_window = tk.Toplevel(add_window)
iccard_info_window.title("新增IC卡")
tk.Label(iccard_info_window, text="卡号:").grid(row=0, column=0)
card_number_entry = tk.Entry(iccard_info_window)
card_number_entry.grid(row=0, column=1)
tk.Label(iccard_info_window, text="用户ID:").grid(row=1, column=0)
user_id_entry = tk.Entry(iccard_info_window)
user_id_entry.grid(row=1, column=1)
tk.Label(iccard_info_window, text="余额:").grid(row=2, column=0)
balance_entry = tk.Entry(iccard_info_window)
balance_entry.grid(row=2, column=1)
tk.Label(iccard_info_window, text="发行日期:").grid(row=3, column=0)
issue_date_entry = tk.Entry(iccard_info_window)
issue_date_entry.grid(row=3, column=1)
tk.Label(iccard_info_window, text="最后充值日期:").grid(row=4, column=0)
last_recharge_date_entry = tk.Entry(iccard_info_window)
last_recharge_date_entry.grid(row=4, column=1)
def save_iccard():
card_number = card_number_entry.get()
user_id = user_id_entry.get()
balance = balance_entry.get()
issue_date = issue_date_entry.get()
last_recharge_date = last_recharge_date_entry.get()
@run_in_thread
def insert_iccard():
with connect_db() as cursor:
query = "INSERT INTO ICCard (CardNumber, UserID, Balance, IssueDate, LastRechargeDate) VALUES (%s, %s, %s, %s, %s)"
cursor.execute(query, (card_number, user_id, balance, issue_date, last_recharge_date))
messagebox.showinfo("成功", "IC卡已新增")
iccard_info_window.destroy()
insert_iccard()
tk.Button(iccard_info_window, text="保存", command=save_iccard).grid(row=5, column=0, columnspan=2)
tk.Button(add_window, text="新增IC卡", command=add_iccard).pack()
# 主界面
def main_interface():
root = tk.Tk()
root.title("物业管理系统")
tk.Label(root, text="欢迎使用物业管理系统").pack()
tk.Button(root, text="用户登录", command=user_login).pack()
tk.Button(root, text="管理员登录", command=admin_login).pack()
root.mainloop()
# 启动主界面
main_interface()
490
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
