再优化一下界面 def get_statistics(self):
"""获取统计结果(增强版)"""
result = {"text": "", "columns": [], "rows": []}
# 总档数统计(所有类别)
self.cursor.execute("SELECT COUNT(DISTINCT file_number) FROM documents")
doc_count = self.cursor.fetchone()[0]
self.cursor.execute("SELECT COUNT(DISTINCT file_number) FROM infrastructure_summary")
infra_summary_count = self.cursor.fetchone()[0]
self.cursor.execute("SELECT COUNT(DISTINCT file_number) FROM infrastructure_detail")
infra_detail_count = self.cursor.fetchone()[0]
total_records = doc_count + infra_summary_count
# 添加总档数统计
result["text"] += f"文书类档案总数: {doc_count}\n"
result["text"] += f"基建类总表数: {infra_summary_count}\n"
result["text"] += f"基建类明细数: {infra_detail_count}\n"
result["text"] += f"总档案数: {total_records}\n"
if self.current_category == "文书类":
# 文书类按年份统计(包含其他年份)
self.cursor.execute("""
SELECT
CASE
WHEN SUBSTR(file_number, INSTR(file_number, '·') + 1, 4) GLOB '[0-9][0-9][0-9][0-9]'
THEN SUBSTR(file_number, INSTR(file_number, '·') + 1, 4)
ELSE '其他年份'
END AS year,
COUNT(DISTINCT file_number) AS file_count
FROM documents
GROUP BY year
ORDER BY CASE WHEN year = '其他年份' THEN 1 ELSE 0 END, year
""")
year_stats = self.cursor.fetchall()
result["text"] += "文书类档案按年份统计:\n"
result["columns"] = ["年份", "档号数量"]
result["rows"] = year_stats
result["text"] += "年份\t档号数量\n"
for row in year_stats:
result["text"] += f"{row[0]}\t{row[1]}\n"
else:
if self.current_infra_view == "总表":
# 总表按保存库房统计(包含空值处理)
self.cursor.execute("""
SELECT
CASE
WHEN storage_location IS NULL OR TRIM(storage_location) = ''
THEN '其他库房'
ELSE storage_location
END AS location,
COUNT(*) AS count
FROM infrastructure_summary
GROUP BY location
ORDER BY location
""")
location_stats = self.cursor.fetchall()
result["text"] += "基建类总表按保存库房统计:\n"
result["columns"] = ["保存库房", "数量"]
result["rows"] = location_stats
result["text"] += "保存库房\t数量\n"
for row in location_stats:
result["text"] += f"{row[0]}\t{row[1]}\n"
else:
# 明细按档号统计
self.cursor.execute("""
SELECT file_number, COUNT(*) AS count
FROM infrastructure_detail
GROUP BY file_number
ORDER BY file_number
""")
file_stats = self.cursor.fetchall()
result["text"] += "基建类明细按档号统计:\n"
result["columns"] = ["档号", "数量"]
result["rows"] = file_stats
result["text"] += "档号\t数量\n"
for row in file_stats:
result["text"] += f"{row[0]}\t{row[1]}\n"
return result
def show_statistics(self):
"""显示美观的统计结果(增强版)"""
dialog = tk.Toplevel(self.root)
dialog.title("档案统计")
self.center_window(dialog, 800, 600) # 增加高度以容纳更多内容
dialog.resizable(True, True) # 允许调整大小
dialog.transient(self.root)
dialog.grab_set()
dialog.configure(bg="#f0f2f5")
# 标题栏
header = tk.Frame(dialog, bg="#4285f4", height=50)
header.pack(fill=tk.X)
header.pack_propagate(False)
tk.Label(header, text="档案统计结果", font=("Microsoft YaHei", 14, "bold"),
bg="#4285f4", fg="white").pack(pady=12)
# 内容区域
content_frame = tk.Frame(dialog, bg="#f0f2f5", padx=20, pady=15)
content_frame.pack(fill=tk.BOTH, expand=True)
# 获取统计结果
stats_data = self.get_statistics()
# 创建统计总览区域
overview_frame = tk.Frame(content_frame, bg="#f0f2f5", pady=10)
overview_frame.pack(fill=tk.X)
# 总览标题
tk.Label(overview_frame, text="档案统计总览", font=("Microsoft YaHei", 12, "bold"),
bg="#f0f2f5", fg="#4285f4").pack(anchor="w")
# 总览内容
overview_text = tk.Text(overview_frame, height=5, width=80, font=("Microsoft YaHei", 10),
bg="white", relief="flat", padx=10, pady=10)
overview_text.pack(fill=tk.X, pady=5)
# 提取总览部分
overview_lines = stats_data["text"].split('\n')[:5] # 获取前5行作为总览
overview_content = "\n".join(overview_lines)
overview_text.insert(tk.END, overview_content)
overview_text.config(state=tk.DISABLED)
# 详细统计区域
detail_frame = tk.LabelFrame(content_frame, text="详细统计", font=("Microsoft YaHei", 11, "bold"),
bg="#f0f2f5", fg="#5f6368", padx=10, pady=10)
detail_frame.pack(fill=tk.BOTH, expand=True, pady=10)
# 创建表格容器
table_container = tk.Frame(detail_frame, bg="#e0e0e0", padx=1, pady=1)
table_container.pack(fill=tk.BOTH, expand=True)
# 创建表格
tree = ttk.Treeview(
table_container,
columns=stats_data["columns"],
show="headings",
style="Stats.Treeview"
)
# 配置列
for col in stats_data["columns"]:
tree.heading(col, text=col)
tree.column(col, anchor=tk.CENTER, width=150)
# 添加滚动条
scrollbar_y = ttk.Scrollbar(table_container, orient=tk.VERTICAL, command=tree.yview)
scrollbar_y.pack(side=tk.RIGHT, fill=tk.Y)
tree.configure(yscrollcommand=scrollbar_y.set)
scrollbar_x = ttk.Scrollbar(table_container, orient=tk.HORIZONTAL, command=tree.xview)
scrollbar_x.pack(side=tk.BOTTOM, fill=tk.X)
tree.configure(xscrollcommand=scrollbar_x.set)
tree.pack(fill=tk.BOTH, expand=True)
# 插入数据
for i, row in enumerate(stats_data["rows"]):
tag = "even" if i % 2 == 0 else "odd"
tree.insert("", tk.END, values=row, tags=(tag,))
# 配置行样式
tree.tag_configure("odd", background="#ffffff")
tree.tag_configure("even", background="#f8f9fa")
最新发布