你当前的代码存在 **两个核心问题**:
---
### ❌ 问题 1:`all_display_options` 等变量在函数内定义,但被用于 `refresh_summary` 后面的逻辑(如 Combobox)
> 虽然不是报错主因,但它暴露了作用域混乱的问题。
---
### ❌ 问题 2(最关键):你在使用 `ck_item_combobox` 的事件回调中引用的是旧版逻辑!
```python
def on_type_in_combobox(event):
typed = ck_item_var.get().lower() # ❌ 错了!应该用 ck_item_display_var
```
而且你的 `on_customer_ref_selected` 回调仍然绑定的是 `ck_item_var`,但实际选中的是带括号的显示文本(如 `"CK-123 (MAT-A1)"`),导致无法正确匹配!
---
## ✅ 正确做法
我们来 **整合并修复完整代码**,实现以下目标:
| 功能 | 已修复 |
|------|--------|
| ✅ 下拉项显示为 `客户料号 (内部料号)` | ✔️ |
| ✅ 输入时支持模糊搜索(客户料号或内部料号) | ✔️ |
| ✅ 1 秒防抖过滤候选列表 | ✔️ |
| ✅ 选中后自动提取真实客户料号和内部料号 | ✔️ |
| ✅ 内部料号只读框同步更新 | ✔️ |
| ✅ 防止重复打开、刷新统计不翻倍 | ✔️ |
---
# ✅ 完整修复后的 `open_customer_receipt_window()` 函数
```python
# ========================================
# 📦 客户签收登记管理窗口(防重复打开)—— 增强版:客户料号下拉 + 显示内部料号 + 防抖搜索
# ========================================
customer_receipt_open = False
def open_customer_receipt_window():
global customer_receipt_open
if customer_receipt_open:
messagebox.showwarning("提示", "客户签收登记窗口已打开。")
return
customer_receipt_open = True
window = tk.Toplevel(root)
window.title("客户签收登记管理")
window.geometry("1200x680")
window.resizable(True, True)
# --- Tk Variables ---
ck_code_var = tk.StringVar()
ck_item_var = tk.StringVar() # 存储真实客户料号(用于数据库)
ck_item_display_var = tk.StringVar() # 显示用:"CK-12345 (MAT-ABC)"
qty_var = tk.StringVar()
location_var = tk.StringVar()
ck_date_var = tk.StringVar()
remark_var = tk.StringVar()
search_var = tk.StringVar()
start_date_var = tk.StringVar(value=(datetime.now() - timedelta(days=30)).strftime("%Y-%m-%d"))
end_date_var = tk.StringVar(value=datetime.now().strftime("%Y-%m-%d"))
# --- 主列表 Treeview ---
tree = ttk.Treeview(
window,
columns=("登记号", "客户料号", "数量", "地点", "打印日期", "登记日期", "备注"),
show="headings"
)
headers = ["登记号", "客户料号", "数量", "地点", "打印日期", "登记日期", "备注"]
widths = [140, 140, 80, 120, 110, 110, 160]
for h, w in zip(headers, widths):
tree.heading(h, text=h)
tree.column(h, width=w, anchor='center')
tree.grid(row=8, column=0, columnspan=4, padx=10, pady=10, sticky="nsew")
v_scroll = ttk.Scrollbar(window, orient="vertical", command=tree.yview)
h_scroll = ttk.Scrollbar(window, orient="horizontal", command=tree.xview)
tree.configure(yscrollcommand=v_scroll.set, xscrollcommand=h_scroll.set)
v_scroll.grid(row=8, column=4, sticky='ns')
h_scroll.grid(row=9, column=0, columnspan=4, sticky='ew')
# =============================
# ✅ 刷新主列表
# =============================
def refresh_list(filter_text=""):
for row in tree.get_children():
tree.delete(row)
try:
if filter_text.strip():
query = """SELECT CK_Code, CK_Item, Qty, Location, CK_Date, Date, Ref
FROM CK_receipt WHERE CK_Code LIKE %s OR CK_Item LIKE %s"""
term = f"%{filter_text}%"
cursor.execute(query, (term, term))
else:
cursor.execute("SELECT CK_Code, CK_Item, Qty, Location, CK_Date, Date, Ref FROM CK_receipt")
for row in cursor.fetchall():
tree.insert("", "end", values=[str(cell or "") for cell in row])
except Exception as e:
messagebox.showerror("读取失败", f"加载数据出错:{e}")
# =============================
# ✅ 新增记录
# =============================
def add_record():
code = ck_code_var.get().strip()
item = ck_item_var.get().strip() # 真实客户料号
qty_str = qty_var.get().strip()
loc = location_var.get().strip()
date_print = ck_date_var.get().strip()
remark = remark_var.get().strip()
if not all([code, item, qty_str]):
messagebox.showwarning("提示", "请填写【登记号】、【客户料号】和【数量】")
return
try:
qty = float(qty_str)
if qty <= 0: raise ValueError
except ValueError:
messagebox.showwarning("提示", "数量必须是大于0的有效数字")
return
try:
cursor.execute("SELECT COUNT(*) FROM CK_receipt WHERE CK_Code = %s", (code,))
if cursor.fetchone()[0] > 0:
messagebox.showerror("冲突", f"❌ 登记号 '{code}' 已存在!")
return
except Exception as e:
messagebox.showerror("检查失败", f"无法验证登记号唯一性:{e}")
return
try:
parsed_date = datetime.strptime(date_print, "%Y-%m-%d")
formatted_date = parsed_date.strftime("%Y-%m-%d")
ck_date_var.set(formatted_date)
except ValueError:
messagebox.showwarning("日期错误", "请输入有效的日期,格式为:YYYY-MM-DD")
return
exists, _ = is_customer_ref_in_master(item)
if not exists:
messagebox.showerror("校验失败", f"客户料号 '{item}' 不存在于主数据中,请核对输入。")
return
register_date = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
try:
cursor.execute("""
INSERT INTO CK_receipt (CK_Code, CK_Item, Qty, Location, CK_Date, Date, Ref)
VALUES (%s, %s, %s, %s, %s, %s, %s)
""", (code, item, qty, loc, formatted_date, register_date, remark))
conn.commit()
messagebox.showinfo("成功", "✅ 记录已添加")
clear_inputs()
refresh_list()
refresh_summary()
except Exception as e:
conn.rollback()
messagebox.showerror("添加失败", f"数据库错误:\n{e}")
# =============================
# ✅ 删除记录
# =============================
def delete_record():
selected = tree.selection()
if not selected:
messagebox.showwarning("提示", "请先选择要删除的记录")
return
values = tree.item(selected[0])['values']
code = values[0]
item = values[1]
if not messagebox.askyesno("确认删除", f"确定要删除?\n登记号:{code}\n客户料号:{item}"):
return
try:
cursor.execute("DELETE FROM CK_receipt WHERE CK_Code = %s", (code,))
conn.commit()
messagebox.showinfo("成功", "🗑️ 记录已删除")
refresh_list()
refresh_summary()
except Exception as e:
conn.rollback()
messagebox.showerror("删除失败", f"数据库错误:\n{e}")
# =============================
# ✅ 清空输入框
# =============================
def clear_inputs():
ck_code_var.set("")
ck_item_var.set("")
ck_item_display_var.set("")
qty_var.set("")
location_var.set("")
ck_date_var.set("")
remark_var.set("")
internal_code_label.config(state='normal')
internal_code_label.delete(0, tk.END)
internal_code_label.config(state='readonly')
# =============================
# ✅ 复制功能
# =============================
def copy_selected():
selected = tree.selection()
if not selected: return
values = tree.item(selected[0])['values']
text = '\t'.join(str(v) for v in values)
window.clipboard_clear()
window.clipboard_append(text)
# =============================
# ✅ 导出 Excel
# =============================
def export_to_excel():
try:
cursor.execute("SELECT CK_Code, CK_Item, Qty, Location, CK_Date, Date, Ref FROM CK_receipt")
df = pd.DataFrame(cursor.fetchall(), columns=["登记号", "客户料号", "数量", "地点", "打印日期", "登记日期", "备注"])
filepath = filedialog.asksaveasfilename(defaultextension=".xlsx", filetypes=[("Excel 文件", "*.xlsx")])
if not filepath: return
df.to_excel(filepath, index=False)
messagebox.showinfo("导出成功", f"已成功导出到:\n{filepath}")
except Exception as e:
messagebox.showerror("导出失败", f"无法导出文件:\n{str(e)}")
# =============================
# 🔍 右上角统计区:出库 vs 签收汇总
# =============================
summary_frame = ttk.LabelFrame(window, text="📊 料号签收统计", padding=5)
summary_frame.grid(row=0, column=2, rowspan=4, padx=15, pady=15, sticky='ne')
ttk.Label(summary_frame, text="开始日期").grid(row=0, column=0, padx=5, pady=2, sticky='e')
start_entry = ttk.Entry(summary_frame, textvariable=start_date_var, width=12)
start_entry.grid(row=0, column=1, padx=5, pady=2, sticky='w')
ttk.Label(summary_frame, text="结束日期").grid(row=1, column=0, padx=5, pady=2, sticky='e')
end_entry = ttk.Entry(summary_frame, textvariable=end_date_var, width=12)
end_entry.grid(row=1, column=1, padx=5, pady=2, sticky='w')
def validate_date_input(var, entry):
val = var.get().strip()
if not val: return
try:
dt = datetime.strptime(val, "%Y-%m-%d")
fmt = dt.strftime("%Y-%m-%d")
if val != fmt:
var.set(fmt)
except ValueError:
messagebox.showwarning("格式错误", "日期格式应为 YYYY-MM-DD")
var.set("")
entry.focus_set()
start_entry.bind("<FocusOut>", lambda e: validate_date_input(start_date_var, start_entry))
end_entry.bind("<FocusOut>", lambda e: validate_date_input(end_date_var, end_entry))
summary_tree = ttk.Treeview(summary_frame, columns=("主料号", "客户料号", "总出库", "总签收"), show="headings", height=8)
for col, w in [("主料号", 100), ("客户料号", 100), ("总出库", 80), ("总签收", 80)]:
summary_tree.heading(col, text=col)
summary_tree.column(col, width=w, anchor='center')
summary_tree.grid(row=3, column=0, columnspan=2, pady=5)
# =============================
# ✅ 刷新统计(防止笛卡尔积)
# =============================
def refresh_summary():
for row in summary_tree.get_children():
summary_tree.delete(row)
start = start_date_var.get().strip()
end = end_date_var.get().strip()
try:
datetime.strptime(start, "%Y-%m-%d")
datetime.strptime(end, "%Y-%m-%d")
except ValueError:
messagebox.showwarning("日期错误", "请使用 YYYY-MM-DD 格式输入时间范围")
return
try:
query = """
SELECT
mm.material_code,
mm.CustomerRef,
COALESCE(out_stats.total_out, 0) AS total_outbound,
COALESCE(cr_stats.total_recv, 0) AS total_received
FROM material_master mm
LEFT JOIN (
SELECT o.material_code, SUM(o.quantity) AS total_out
FROM outbound o
WHERE o.date_out BETWEEN %s AND %s
GROUP BY o.material_code
) out_stats ON mm.material_code = out_stats.material_code
LEFT JOIN (
SELECT cr.CK_Item, SUM(cr.Qty) AS total_recv
FROM CK_receipt cr
WHERE cr.CK_Date BETWEEN %s AND %s
GROUP BY cr.CK_Item
) cr_stats ON mm.CustomerRef = cr_stats.CK_Item
WHERE mm.CustomerRef IS NOT NULL AND mm.CustomerRef != ''
AND (out_stats.total_out > 0 OR cr_stats.total_recv > 0)
ORDER BY mm.material_code, mm.CustomerRef
"""
cursor.execute(query, (start, end, start, end))
for row in cursor.fetchall():
summary_tree.insert("", "end", values=[
str(row[0]), str(row[1]),
f"{float(row[2]):g}", f"{float(row[3]):g}"
])
except Exception as e:
messagebox.showerror("统计失败", f"加载统计数据失败:\n{e}")
ttk.Button(summary_frame, text="🔄 刷新统计", command=refresh_summary).grid(row=2, column=0, columnspan=2, pady=5)
# =============================
# ✅ 加载客户料号映射表(含内部料号)
# =============================
all_display_options = [] # 显示文本:"CK-123 (MAT-A1)"
display_to_ref_map = {} # 显示 → 客户料号
ref_to_internal_map = {} # 客户料号 → 内部料号
try:
cursor.execute("""
SELECT CustomerRef, material_code
FROM material_master
WHERE CustomerRef IS NOT NULL AND CustomerRef != ''
ORDER BY CustomerRef
""")
rows = cursor.fetchall()
for ref, code in rows:
display_text = f"{ref} ({code})"
all_display_options.append(display_text)
display_to_ref_map[display_text] = ref
ref_to_internal_map[ref] = code
except Exception as e:
messagebox.showwarning("警告", f"无法加载客户料号映射表:{e}")
all_display_options = []
display_to_ref_map = {}
ref_to_internal_map = {}
# =============================
# UI 布局
# =============================
input_frame = ttk.Frame(window)
input_frame.grid(row=0, rowspan=8, column=0, columnspan=2, padx=10, pady=10, sticky="nw")
# 客户料号下拉框
ttk.Label(input_frame, text="客户料号").grid(row=1, column=0, padx=10, pady=4, sticky='e')
ck_item_combobox = ttk.Combobox(
input_frame,
textvariable=ck_item_display_var,
values=all_display_options,
width=35,
state="normal"
)
ck_item_combobox.grid(row=1, column=1, padx=10, pady=4, sticky='w')
# 内部料号只读框
ttk.Label(input_frame, text="内部料号").grid(row=7, column=0, padx=10, pady=4, sticky='e')
internal_code_label = ttk.Entry(input_frame, state='readonly', width=25)
internal_code_label.grid(row=7, column=1, padx=10, pady=4, sticky='w')
# 打印日期带日历选择器
ttk.Label(input_frame, text="打印日期").grid(row=3, column=0, padx=10, pady=4, sticky='e')
date_frame = ttk.Frame(input_frame)
date_frame.grid(row=3, column=1, padx=10, pady=4, sticky='w')
entry_ck_date = ttk.Entry(date_frame, textvariable=ck_date_var, width=15)
entry_ck_date.pack(side="left")
tk.Button(date_frame, text="📅", font=("Arial", 9), width=3,
command=lambda: show_date_picker(entry_ck_date, ck_date_var)).pack(side="left", padx=(5, 0))
entry_ck_date.bind("<FocusOut>", lambda e: validate_date_blur(ck_date_var))
# 其他字段
field_mapping = {
"登记号": (ck_code_var, 0),
"数量": (qty_var, 2),
"地点": (location_var, 4),
"备注": (remark_var, 5),
}
for label, (var, row_idx) in field_mapping.items():
if label == "登记号":
continue
ttk.Label(input_frame, text=label).grid(row=row_idx, column=0, padx=10, pady=4, sticky='e')
ttk.Entry(input_frame, textvariable=var, width=25).grid(row=row_idx, column=1, padx=10, pady=4, sticky='w')
ttk.Label(input_frame, text="* 登记日期自动记录", foreground="gray", font=("Arial", 10)) \
.grid(row=6, column=0, columnspan=2, padx=10, pady=2, sticky='w')
# =============================
# ✅ 下拉框事件处理(含防抖)
# =============================
debounce_id = None # 用于取消上一次延迟任务
def perform_filter():
nonlocal debounce_id
typed = ck_item_display_var.get().lower()
if not typed:
ck_item_combobox['values'] = all_display_options
return
filtered = [opt for opt in all_display_options if typed in opt.lower()]
ck_item_combobox['values'] = filtered
if filtered:
ck_item_combobox.event_generate('<Down>')
def on_key_release(event):
nonlocal debounce_id
if event.keysym in ('Up', 'Down', 'Return', 'Escape'):
return
if debounce_id:
ck_item_combobox.after_cancel(debounce_id)
debounce_id = ck_item_combobox.after(1000, perform_filter)
def on_selection_or_blur(event=None):
display_val = ck_item_display_var.get().strip()
if display_val in display_to_ref_map:
real_ref = display_to_ref_map[display_val]
ck_item_var.set(real_ref)
internal_code = ref_to_internal_map.get(real_ref, "未知")
internal_code_label.config(state='normal')
internal_code_label.delete(0, tk.END)
internal_code_label.insert(0, internal_code)
internal_code_label.config(state='readonly')
else:
# 尝试模糊匹配最接近的一项
for full in all_display_options:
if display_val.lower() in full.lower():
real_ref = display_to_ref_map[full]
ck_item_var.set(real_ref)
internal_code = ref_to_internal_map.get(real_ref, "未知")
internal_code_label.config(state='normal')
internal_code_label.delete(0, tk.END)
internal_code_label.insert(0, internal_code)
internal_code_label.config(state='readonly')
ck_item_display_var.set(full)
return
# 没找到
internal_code_label.config(state='normal')
internal_code_label.delete(0, tk.END)
internal_code_label.insert(0, "未匹配")
internal_code_label.config(state='readonly')
# 绑定事件
ck_item_combobox.bind("<KeyRelease>", on_key_release)
ck_item_combobox.bind("<<ComboboxSelected>>", on_selection_or_blur)
ck_item_combobox.bind("<FocusOut>", on_selection_or_blur)
ck_item_combobox.bind("<Return>", on_selection_or_blur)
# =============================
# 按钮区
# =============================
button_frame = ttk.Frame(window)
button_frame.grid(row=7, column=0, columnspan=4, pady=10, sticky='w', padx=10)
ttk.Button(button_frame, text="➕ 添加记录", command=add_record).pack(side="left", padx=5)
ttk.Button(button_frame, text="🗑️ 删除选中", command=delete_record).pack(side="left", padx=5)
ttk.Button(button_frame, text="📄 导出为Excel", command=export_to_excel).pack(side="left", padx=5)
# 搜索框
ttk.Label(window, text="🔍 搜索").grid(row=7, column=2, padx=10, pady=9, sticky='e')
ttk.Entry(window, textvariable=search_var, width=30).grid(row=7, column=3, padx=10, pady=9, sticky='w')
search_var.trace_add("write", lambda *args: refresh_list(search_var.get()))
# 右键菜单
context_menu = Menu(window, tearoff=0)
context_menu.add_command(label="📋 复制整行", command=copy_selected)
tree.bind("<Button-3>", lambda e: context_menu.post(e.x_root, e.y_root))
# 初始化
refresh_list()
refresh_summary()
# 关闭事件
def on_close():
global customer_receipt_open
customer_receipt_open = False
window.destroy()
window.protocol("WM_DELETE_WINDOW", on_close)
window.grid_rowconfigure(8, weight=1)
window.grid_columnconfigure(1, weight=1)
window.transient(root)
window.grab_set()
```
---
## ✅ 重点说明
| 特性 | 实现方式 |
|------|----------|
| 💡 显示带内部料号 | `f"{ref} ({code})"` |
| ⏱ 防抖 1 秒 | `after(1000, ...)` + `after_cancel` |
| 🔗 显示 ↔ 真实值 | `display_to_ref_map` 映射 |
| 🧠 自动补全+回填 | `<KeyRelease>` + `<<ComboboxSelected>>` + `<FocusOut>` |
| ✅ 数据库存纯客户料号 | 使用 `ck_item_var` 存真实值 |
---
## ✅ 你需要确保已有辅助函数
```python
def validate_date_blur(var):
val = var.get().strip()
if not val: return
try:
dt = datetime.strptime(val, "%Y-%m-%d")
fmt = dt.strftime("%Y-%m-%d")
if val != fmt:
var.set(fmt)
except ValueError:
messagebox.showwarning("格式错误", "日期格式应为 YYYY-MM-DD")
var.set("")
def show_date_picker(entry_widget, var):
def pick_date():
date_str = cal.get_date()
var.set(date_str)
top.destroy()
entry_widget.focus_set()
top = tk.Toplevel(window)
top.title("选择日期")
cal = Calendar(top, selectmode='day', date_pattern='yyyy-mm-dd')
cal.pack(padx=10, pady=10)
ttk.Button(top, text="确定", command=pick_date).pack(pady=5)
center_window(top)
```
---