from tkinter import ttk, messagebox, filedialog
from datetime import datetime
import pymysql
import pandas as pd
import os
import re
class ProjectNumberSystem:
def __init__(self, root):
self.root = root
self.root.title("项目编号管理系统")
self.root.geometry("1400x900")
self.root.configure(bg="white")
# 应用简洁风格
self.apply_clean_style()
# 初始化数据库连接
self.connection = self.connect_to_db()
# 创建主框架
self.create_main_frame()
# 初始化取号界面
self.create_generate_section()
# 初始化数据展示界面
self.create_data_section()
# 加载初始数据
self.set_current_date()
self.load_project_types()
self.load_handlers() # 加载经办人和科室
def apply_clean_style(self):
"""应用简洁风格"""
self.style = ttk.Style()
self.style.theme_use('clam')
# 配置全局样式
self.style.configure(".", background="white", font=("Arial", 10))
self.style.configure("TFrame", background="white")
self.style.configure("TLabel", background="white", foreground="#333")
self.style.configure("TButton",
foreground="white",
background="#3498db",
padding=8,
borderwidth=0)
self.style.map("TButton",
background=[('active', '#2980b9')])
# 配置Treeview
self.style.configure("Treeview",
background="white",
foreground="#333",
rowheight=28,
fieldbackground="white",
borderwidth=0)
self.style.configure("Treeview.Heading",
background="#f0f0f0",
foreground="#333",
font=("Arial", 9, "bold"))
self.style.map("Treeview",
background=[('selected', '#e6f2ff')])
# 配置输入框
self.style.configure("TEntry", fieldbackground="white")
self.style.configure("TCombobox", fieldbackground="white")
def connect_to_db(self):
"""连接数据库"""
try:
return pymysql.connect(host="", user="root",password="123456",database="projectnum",cursorclass=pymysql.cursors.DictCursor)
except Exception as e:
messagebox.showerror("数据库错误", f"连接失败: {e}")
return None
def create_main_frame(self):
"""创建主框架"""
self.main_frame = ttk.Frame(self.root, padding=20)
self.main_frame.pack(fill='both', expand=True, padx=20, pady=20)
# 创建选项卡
self.notebook = ttk.Notebook(self.main_frame)
self.notebook.pack(fill='both', expand=True)
self.notebook.bind(">", self.on_tab_changed)
def create_generate_section(self):
"""创建取号界面"""
self.tab_generate = ttk.Frame(self.notebook)
self.notebook.add(self.tab_generate, text=' 取号生成 ')
# 主卡片容器
card_frame = ttk.LabelFrame(self.tab_generate, text="项目信息", padding=15)
card_frame.pack(fill='both', expand=True, padx=10, pady=10)
# 项目基本信息组
info_frame = ttk.Frame(card_frame)
info_frame.pack(fill='x', pady=5)
# 项目类型
ttk.Label(info_frame, text="项目类型:").grid(row=0, column=0, padx=5, pady=8, sticky="e")
self.type_var = tk.StringVar()
self.type_combobox = ttk.Combobox(info_frame, textvariable=self.type_var,
state="readonly", width=25)
self.type_combobox.grid(row=0, column=1, padx=5, pady=8, sticky="w")
# 项目名称（加大尺寸）
ttk.Label(info_frame, text="项目名称:").grid(row=1, column=0, padx=5, pady=8, sticky="e")
self.project_name_var = tk.StringVar()
project_name_entry = ttk.Entry(info_frame, textvariable=self.project_name_var,
width=35, font=("Arial", 10))
project_name_entry.grid(row=1, column=1, padx=5, pady=8, sticky="ew", columnspan=3)
# 日期显示
ttk.Label(info_frame, text="当前日期:").grid(row=2, column=0, padx=5, pady=8, sticky="e")
self.date_var = tk.StringVar()
date_entry = ttk.Entry(info_frame, textvariable=self.date_var,
state="readonly", width=25)
date_entry.grid(row=2, column=1, padx=5, pady=8, sticky="w")
# 联系人信息组
contact_frame = ttk.LabelFrame(card_frame, text="联系人信息", padding=10)
contact_frame.pack(fill='x', pady=10)
ttk.Label(contact_frame, text="联系人:").grid(row=0, column=0, padx=5, pady=5, sticky="e")
self.lxr_var = tk.StringVar()
lxr_entry = ttk.Entry(contact_frame, textvariable=self.lxr_var, width=25)
lxr_entry.grid(row=0, column=1, padx=5, pady=5)
ttk.Label(contact_frame, text="联系电话:").grid(row=0, column=2, padx=5, pady=5, sticky="e")
self.lxrdh_var = tk.StringVar()
lxrdh_entry = ttk.Entry(contact_frame, textvariable=self.lxrdh_var, width=25)
lxrdh_entry.grid(row=0, column=3, padx=5, pady=5)
# 建设单位信息组
company_frame = ttk.LabelFrame(card_frame, text="建设单位信息", padding=10)
company_frame.pack(fill='x', pady=10)
ttk.Label(company_frame, text="建设单位:").grid(row=0, column=0, padx=5, pady=5, sticky="e")
self.jsdw_var = tk.StringVar()
jsdw_entry = ttk.Entry(company_frame, textvariable=self.jsdw_var, width=25)
jsdw_entry.grid(row=0, column=1, padx=5, pady=5)
ttk.Label(company_frame, text="建设地址:").grid(row=0, column=2, padx=5, pady=5, sticky="e")
self.jsdz_var = tk.StringVar()
jsdz_entry = ttk.Entry(company_frame, textvariable=self.jsdz_var, width=25)
jsdz_entry.grid(row=0, column=3, padx=5, pady=5)
# 经办信息组
handler_frame = ttk.LabelFrame(card_frame, text="经办信息", padding=10)
handler_frame.pack(fill='x', pady=10)
ttk.Label(handler_frame, text="经办科室:").grid(row=0, column=0, padx=5, pady=5, sticky="e")
self.jbks_var = tk.StringVar()
self.jbks_combobox = ttk.Combobox(handler_frame, textvariable=self.jbks_var, width=23,state="readonly")
self.jbks_combobox.grid(row=0, column=1, padx=5, pady=5)
ttk.Label(handler_frame, text="经办人:").grid(row=0, column=2, padx=5, pady=5, sticky="e")
self.jbr_var = tk.StringVar()
self.jbr_combobox = ttk.Combobox(handler_frame, textvariable=self.jbr_var, width=23,state="readonly")
self.jbr_combobox.grid(row=0, column=3, padx=5, pady=5)
# 生成按钮
btn_frame = ttk.Frame(card_frame)
btn_frame.pack(pady=15)
ttk.Button(btn_frame, text="生成项目编号", command=self.generate_number,
width=15).pack()
# 结果显示
result_frame = ttk.LabelFrame(card_frame, text="生成结果", padding=10)
result_frame.pack(fill='x', pady=10)
self.result_var = tk.StringVar(value="等待生成...")
result_label = ttk.Label(result_frame, textvariable=self.result_var,
font=("Arial", 14, "bold"), foreground="#2c3e50")
result_label.pack(pady=5)
def create_data_section(self):
"""创建数据展示界面"""
self.tab_data = ttk.Frame(self.notebook)
self.notebook.add(self.tab_data, text=' 项目记录 ')
# 标题和按钮
header_frame = ttk.Frame(self.tab_data)
header_frame.pack(fill="x", pady=(0, 10))
title_label = ttk.Label(header_frame,
text="项目记录表",
font=("Arial", 14, "bold"),
foreground="#333")
title_label.pack(side="left", padx=(0, 10))
# 操作按钮
btn_frame = ttk.Frame(header_frame)
btn_frame.pack(side="right")
ttk.Button(btn_frame, text="刷新", command=self.load_data).pack(side="left", padx=5)
ttk.Button(btn_frame, text="导出Excel", command=self.export_data).pack(side="left")
# Treeview容器
tree_frame = ttk.Frame(self.tab_data)
tree_frame.pack(fill="both", expand=True)
# 创建Treeview
columns = ("id", "xmlx", "XMMC", "BH", "XMBH",
"lxr", "lxrdh", "JSDW", "JSDZ", "JBKS", "JBR", "qhsj")
self.data_tree = ttk.Treeview(
tree_frame,
columns=columns,
show="headings",
height=15
)
# 配置列
col_configs = [
("id", "ID", 40),
("xmlx", "项目类型", 80),
("XMMC", "项目名称", 150),
("BH", "取号", 50),
("XMBH", "项目编号", 150),
("lxr", "联系人", 80),
("lxrdh", "联系电话", 100),
("JSDW", "建设单位", 120),
("JSDZ", "建设地址", 150),
("JBKS", "经办科室", 100),
("JBR", "经办人", 80),
("qhsj", "取号时间", 120)
]
for col_id, text, width in col_configs:
self.data_tree.heading(col_id, text=text)
self.data_tree.column(col_id, width=width, minwidth=width, anchor="center")
# 添加滚动条
scrollbar = ttk.Scrollbar(tree_frame, orient="vertical", command=self.data_tree.yview)
self.data_tree.configure(yscrollcommand=scrollbar.set)
# 布局
self.data_tree.pack(side="left", fill="both", expand=True)
scrollbar.pack(side="right", fill="y")
def on_tab_changed(self, event):
"""选项卡切换事件处理"""
selected_tab = self.notebook.tab(self.notebook.select(), "text")
if selected_tab == " 项目记录 ":
self.load_data()
def set_current_date(self):
"""设置当前日期"""
now = datetime.now()
self.date_var.set(now.strftime("%Y年%m月%d日"))
def load_project_types(self):
"""从数据库加载项目类型"""
if not self.connection:
return
try:
types = ["A","B","C","D"]
self.type_combobox["values"] = types
if types:
self.type_var.set(types[0])
except Exception as e:
messagebox.showerror("错误", f"加载项目类型失败:\n{e}")
def load_handlers(self):
"""加载经办科室和经办人选项"""
if not self.connection:
return
try:
with self.connection.cursor() as cursor:
# 加载经办科室
jbks_list = ['','','']
self.jbks_combobox["values"] = jbks_list
# 加载经办人
cursor.execute("SELECT DISTINCT name FROM user WHERE name IS NOT NULL AND name != ''")
jbr_list = [row['name'] for row in cursor.fetchall()]
self.jbr_combobox["values"] = jbr_list
except Exception as e:
messagebox.showerror("错误", f"加载经办信息失败:\n{e}")
def get_max_bh(self, project_type):
"""获取指定项目类型的当天最大取号(BH)"""
if not self.connection:
return None
try:
with self.connection.cursor() as cursor:
# 获取当前日期字符串（用于查询）
current_date = datetime.now().strftime("%Y-%m-%d")
# 查询当天该类型的最大取号
sql = """
SELECT MAX(BH) AS max_bh
FROM qh
WHERE xmlx = %s AND DATE(qhsj) = %s
"""
cursor.execute(sql, (project_type, current_date))
result = cursor.fetchone()
return result['max_bh'] if result['max_bh'] is not None else 0
except Exception as e:
messagebox.showerror("数据库错误", f"查询最大取号时出错:\n{e}")
return None
def generate_number(self):
"""生成项目编号并插入数据库"""
if not self.connection:
messagebox.showerror("错误", "数据库未连接")
return
# 收集表单数据
project_type = self.type_var.get()
if(project_type =="A"):
project_type_BH ="WA"
elif(project_type =="B"):
project_type_BH ="WB"
elif (project_type == "C"):
project_type_BH = "WC"
else :
project_type_BH = "WD"
project_name = self.project_name_var.get().strip()
lxr = self.lxr_var.get().strip()
lxrdh = self.lxrdh_var.get().strip()
jsdw = self.jsdw_var.get().strip()
jsdz = self.jsdz_var.get().strip()
jbks = self.jbks_var.get().strip()
jbr = self.jbr_var.get().strip()
# 验证必填字段
if not project_type:
messagebox.showwarning("警告", "请选择项目类型")
return
if not project_name:
messagebox.showwarning("警告", "请输入项目名称")
return
#正则判断电话号码
pattern = r'^1[3-9]\d{9}$'
if not re.match(pattern, lxrdh) :
messagebox.showwarning("警告", "请输入正确的联系电话")
return
if not jbks:
messagebox.showwarning("警告", "请填写经办科室")
return
if not jbr:
messagebox.showwarning("警告", "请填写经办人")
return
# 获取当前最大取号
max_bh = self.get_max_bh(project_type)
if max_bh is None:
return
# 计算新取号
new_bh = max_bh + 1
try:
with self.connection.cursor() as cursor:
# 生成完整项目编号（XMBH）
date_str = datetime.now().strftime("%Y")
xmbh = f"{date_str}{new_bh:05d}{project_type_BH}"
# 插入新记录（包含所有新字段）
sql = """
INSERT INTO qh (
xmlx, XMMC, BH, XMBH, qhsj,
lxr, lxrdh, JSDW, JSDZ, JBKS, JBR
) VALUES (%s, %s, %s, %s, NOW(), %s, %s, %s, %s, %s, %s)
"""
cursor.execute(sql, (
project_type, project_name, new_bh, xmbh,
lxr, lxrdh, jsdw, jsdz, jbks, jbr
))
self.connection.commit()
# 显示结果
self.result_var.set(xmbh)
messagebox.showinfo("成功", f"已生成新编号: {xmbh}")
# 清空表单
self.project_name_var.set("")
self.lxr_var.set("")
self.lxrdh_var.set("")
self.jsdw_var.set("")
self.jsdz_var.set("")
# 如果当前在数据选项卡，刷新数据
if self.notebook.tab(self.notebook.select(), "text") == " 项目记录 ":
self.load_data()
# 刷新经办选项
self.load_handlers()
except Exception as e:
self.connection.rollback()
messagebox.showerror("数据库错误", f"保存编号时出错:\n{e}")
def load_data(self):
"""加载数据库记录到Treeview"""
if not self.connection:
return
# 清空现有数据
for item in self.data_tree.get_children():
self.data_tree.delete(item)
try:
with self.connection.cursor() as cursor:
# 查询数据并按时间降序排序
sql = """
SELECT
id, xmlx, XMMC, BH, XMBH,
lxr, lxrdh, JSDW, JSDZ, JBKS, JBR, qhsj
FROM qh
ORDER BY qhsj DESC
"""
cursor.execute(sql)
rows = cursor.fetchall()
# 添加数据到Treeview
for row in rows:
# 格式化时间
qhsj = row['qhsj']
if isinstance(qhsj, datetime):
qhsj = qhsj.strftime("%Y-%m-%d %H:%M")
self.data_tree.insert("", "end", values=(
row['id'],
row['xmlx'],
row.get('XMMC', ''),
row['BH'],
row['XMBH'],
row.get('lxr', ''),
row.get('lxrdh', ''),
row.get('JSDW', ''),
row.get('JSDZ', ''),
row.get('JBKS', ''),
row.get('JBR', ''),
qhsj
))
except Exception as e:
messagebox.showerror("数据库错误", f"加载数据失败:\n{e}")
def export_data(self):
"""导出数据到Excel文件"""
if not self.connection:
messagebox.showerror("错误", "数据库未连接")
return
try:
# 获取保存路径
file_path = filedialog.asksaveasfilename(
defaultextension=".xlsx",
filetypes=[("Excel文件", "*.xlsx"), ("所有文件", "*.*")],
title="保存为Excel文件"
)
if not file_path:
return # 用户取消了保存
# 查询数据
with self.connection.cursor() as cursor:
sql = """
SELECT
id, xmlx 项目类型, XMMC 项目名称, BH 编号, XMBH 项目编号,
lxr 联系人, lxrdh 联系人电话, JSDW 建设单位, JSDZ 建设地址, JBKS 经办科室, JBR 经办人, qhsj 取号时间
FROM qh
ORDER BY qhsj DESC
"""
cursor.execute(sql)
data = cursor.fetchall()
if not data:
messagebox.showwarning("警告", "没有数据可导出")
return
# 转换为DataFrame
df = pd.DataFrame(data)
# 导出到Excel
df.to_excel(file_path, index=False)
messagebox.showinfo("导出成功", f"数据已成功导出到:\n{file_path}\n")
# 打开文件所在目录
# if messagebox.show("导出成功", f"数据已成功导出到:\n{file_path}\n\n是否打开所在目录？"):
# os.startfile(os.path.dirname(file_path))
except Exception as e:
messagebox.showerror("导出错误", f"导出数据时出错:\n{e}")
if __name__ == "__main__":
root = tk.Tk()
app = ProjectNumberSystem(root)
root.mainloop()
演示效果：
