# ========================================
# 📦 客户签收登记管理窗口（防重复打开）—— 增强版：客户料号下拉 + 显示内部料号 + 防抖搜索
# ========================================
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()
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):
"""
简易日历选择器，返回 YYYY-MM-DD 格式日期
"""
picker = tk.Toplevel()
picker.title("选择日期")
picker.geometry("280x250")
picker.resizable(False, False)
picker.transient()
picker.grab_set()
today = datetime.now()
current = [today.year, today.month] # 可变列表用于内部修改
header = tk.Frame(picker)
header.pack(pady=5)
label_month_year = tk.Label(header, text="", font=("Arial", 12, "bold"))
label_month_year.grid(row=0, column=1)
def update_calendar():
label_month_year.config(text=f"{current[0]}年 {current[1]}月")
for widget in calendar_frame.winfo_children():
widget.destroy()
# 星期标题
weekdays = ['日', '一', '二', '三', '四', '五', '六']
for i, day in enumerate(weekdays):
tk.Label(calendar_frame, text=day, fg="darkgray").grid(row=0, column=i)
import calendar
cal = calendar.monthrange(current[0], current[1])
start_day = cal[0]
max_days = cal[1]
# Python calendar: Monday=0 → Sunday=6 → 我们要 Sunday=0 开头
start_col = (start_day + 1) % 7
row, col = 1, start_col
for day in range(1, max_days + 1):
btn = tk.Button(
calendar_frame,
text=str(day),
width=3,
height=1,
command=lambda d=day: select_date(d)
)
btn.grid(row=row, column=col, padx=1, pady=1)
if current[0] == today.year and current[1] == today.month and day == today.day:
btn.config(bg="#ffe4e1") # 高亮今天
col += 1
if col > 6:
col = 0
row += 1
def select_date(day):
selected_date = f"{current[0]:04d}-{current[1]:02d}-{day:02d}"
var.set(selected_date)
picker.destroy()
entry_widget.focus_set()
def prev_month():
current[1] -= 1
if current[1] == 0:
current[1] = 12
current[0] -= 1
update_calendar()
def next_month():
current[1] += 1
if current[1] == 13:
current[1] = 1
current[0] += 1
update_calendar()
tk.Button(header, text="<", command=prev_month, width=3).grid(row=0, column=0)
tk.Button(header, text=">", command=next_month, width=3).grid(row=0, column=2)
calendar_frame = tk.Frame(picker)
calendar_frame.pack()
update_calendar()
picker.update_idletasks()
x = picker.master.winfo_x() + (picker.master.winfo_width() // 2) - (picker.winfo_width() // 2)
y = picker.master.winfo_y() + (picker.master.winfo_height() // 2) - (picker.winfo_height() // 2)
picker.geometry(f"+{int(x)}+{int(y)}")
##############################################################
def is_customer_ref_in_master(customer_ref):
try:
cursor.execute("SELECT DISTINCT CustomerRef FROM material_master WHERE CustomerRef IS NOT NULL AND CustomerRef != ''")
all_refs = [row[0] for row in cursor.fetchall()]
return customer_ref in all_refs, all_refs
except Exception as e:
messagebox.showerror("数据库错误", f"无法查询主数据表：{e}")
return True, []
def get_material_code_by_customer_ref(customer_ref):
try:
cursor.execute("SELECT material_code FROM material_master WHERE CustomerRef = %s LIMIT 1", (customer_ref,))
result = cursor.fetchone()
return result[0] if result else None
except Exception as e:
messagebox.showerror("查询失败", f"无法查询主数据：\n{e}")
return None
#############################
#############################
def get_material_code_by_customer_ref(customer_ref):
"""
根据 CustomerRef 查询对应的 material_code
返回: str 或 None
"""
try:
cursor.execute(
"SELECT material_code FROM material_master WHERE CustomerRef = %s LIMIT 1",
(customer_ref,)
)
result = cursor.fetchone()
return result[0] if result else None
except Exception as e:
messagebox.showerror("查询失败", f"无法查询主数据：\n{e}")
return None
提供完整修复后的代码
最新发布
本文解决了一个在使用dd下拉时，无法给code字段赋值的问题。关键在于避免在datatype模型中为字段设定默认值。通过移除defaultValue的设置，问题得以解决。
扫一扫
498
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
