# ========================================
# 📦 客户签收登记管理窗口（防重复打开）
# ========================================
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("1180x650")
window.resizable(True, True)
# --- Tk Variables ---
ck_code_var = tk.StringVar()
ck_item_var = tk.StringVar()
qty_var = tk.StringVar()
location_var = tk.StringVar()
ck_date_var = tk.StringVar()
remark_var = tk.StringVar()
search_var = tk.StringVar()
# --- Treeview & Scrollbars ---
tree = ttk.Treeview(window, columns=("登记号", "客户料号", "数量", "地点", "打印日期", "登记日期", "备注"), show="headings")
# ... （省略列定义、滚动条等，保持原样）
headers = ["登记号", "客户料号", "数量", "地点", "打印日期", "登记日期", "备注"]
widths = [150, 150, 80, 120, 120, 120, 180]
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(*), processed FROM CK_receipt WHERE CK_Code = %s GROUP BY processed", (code,))
result = cursor.fetchone()
if result:
count, processed = result
if processed:
messagebox.showerror("已处理", f"❌ 登记号 '{code}' 已被处理且不可重复使用！")
return
else:
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
# ✅ 步骤1：校验客户料号是否存在
exists, _ = is_customer_ref_in_master(item)
if not exists:
messagebox.showerror(
"校验失败",
f"❌ 客户料号 '{item}' 不存在于主数据表中。\n"
f"无法添加该记录。\n\n"
f"请核对输入确认出库客户物料号是否存在。"
)
return
# ✅ 步骤2：通过 CustomerRef 查找 material_code
main_material_code = get_material_code_by_customer_ref(item)
if not main_material_code:
messagebox.showerror("映射失败", f"客户料号 '{item}' 未关联任何主数据料号，请检查主数据配置。")
return
# ✅ 步骤3：查询该料号总的未签收出库数量（此时不包含本条）
unreceived_total = get_unreceived_quantity(main_material_code)
if qty > unreceived_total:
messagebox.showerror(
"超额签收",
f"❌ 签收数量 {qty} 超过当前未签收出库总量 {unreceived_total:g}！\n\n"
f"请核实是否已有重复签收或出库数据异常。"
)
return
# ✅ 步骤4：构建新备注
match_info = f"出库未签收匹配 {qty:g}/{unreceived_total:g}"
final_remark = f"{match_info}; {remark}" if remark.strip() else match_info
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, processed)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
""", (code, item, qty, loc, formatted_date, register_date, final_remark, False))
# ✅ 成功后更新 outbound 表中的 receipt 字段
if update_outbound_receipt_safe(main_material_code, qty, code):
conn.commit()
messagebox.showinfo("成功", "✅ 记录已添加并同步更新出库签收状态")
clear_inputs()
refresh_list()
else:
conn.rollback()
messagebox.showerror("失败", "签收记录保存失败，已回滚。")
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]
qty = float(values[2])
# 查询是否已处理
cursor.execute("SELECT processed FROM CK_receipt WHERE CK_Code = %s", (code,))
row = cursor.fetchone()
if not row or not row[0]:
messagebox.showerror("错误", "该记录尚未处理，不能删除。")
return
if not messagebox.askyesno("确认删除", f"确定要删除？\n登记号：{code}\n客户料号：{item}\n数量：{qty}"):
return
try:
main_material_code = get_material_code_by_customer_ref(item)
if not main_material_code:
messagebox.showerror("错误", "无法找到主料号")
return
# 回滚 receipt
if revert_old_receipt(item, qty):
cursor.execute("DELETE FROM CK_receipt WHERE CK_Code = %s", (code,))
conn.commit()
messagebox.showinfo("成功", "🗑️ 记录已删除并回滚签收")
refresh_list()
else:
conn.rollback()
except Exception as e:
conn.rollback()
messagebox.showerror("失败", f"删除失败：\n{e}")
# =============================
# ✅ 双击编辑
# =============================
def on_double_click(event):
selected = tree.selection()
if not selected:
return
try:
values = tree.item(selected[0])['values']
original_code, original_item = values[0], values[1]
except (IndexError, ValueError) as e:
messagebox.showerror("错误", f"无法读取选中行数据：{e}")
return
edit_win = tk.Toplevel(window)
edit_win.title("编辑客户签收记录")
edit_win.geometry("500x420")
edit_win.resizable(False, False)
edit_win.transient(window) # 置于主窗口上方
edit_win.grab_set() # 模态窗口，阻止操作父窗
# ✅ 定义可编辑字段索引与标签（排除 '登记日期' 自动字段）
editable_indices = [0, 1, 2, 3, 4, 6] # 登记号, 客户料号, 数量, 地点, 打印日期, 备注
editable_labels = ["登记号", "客户料号", "数量", "地点", "打印日期", "备注"]
vars = [tk.StringVar(value=str(values[i]).strip()) for i in editable_indices]
# 添加只读字段用于显示不可编辑项
display_fields = editable_labels + ["登记日期（不可编辑）"]
all_vars = vars + [tk.StringVar(value=values[5])] # values[5] 是登记日期
entries = [] # 存储输入控件以便聚焦或验证
for i, (label, var) in enumerate(zip(display_fields, all_vars)):
ttk.Label(edit_win, text=label).grid(row=i, column=0, padx=15, pady=6, sticky='e')
if "不可编辑" in label:
# 只读 Entry 显示登记日期
entry = ttk.Entry(edit_win, textvariable=var, state='readonly', width=30)
entry.grid(row=i, column=1, padx=15, pady=6, sticky='w')
entries.append(entry)
else:
if label == "打印日期":
# 特殊处理：添加日期选择按钮
date_frame = ttk.Frame(edit_win)
date_frame.grid(row=i, column=1, padx=15, pady=6, sticky='w')
entry = ttk.Entry(date_frame, textvariable=var, width=15)
entry.pack(side="left")
btn = tk.Button(
date_frame,
text="📅",
font=("Arial", 9),
width=3,
command=lambda e=entry, v=var: show_date_picker(e, v)
)
btn.pack(side="left", padx=(5, 0))
add_tooltip(entry, "点击右侧按钮选择日期")
entries.append(entry)
else:
# 普通可编辑字段
entry = ttk.Entry(edit_win, textvariable=var, width=30)
entry.grid(row=i, column=1, padx=15, pady=6, sticky='w')
entries.append(entry)
# 设置第一个输入框获得焦点
edit_win.after(100, lambda: entries[0].focus_set())
# =============================
# ✅ 保存修改函数
# =============================
def save_edit():
try:
new_code = vars[0].get().strip()
new_item = vars[1].get().strip()
new_qty_str = vars[2].get().strip()
new_loc = vars[3].get().strip()
new_ck_date = vars[4].get().strip()
new_remark = vars[5].get().strip()
if not all([new_code, new_item, new_qty_str]):
messagebox.showwarning("提示", "登记号、客户料号、数量不能为空")
return
try:
new_qty = float(new_qty_str)
if new_qty <= 0: raise ValueError
except ValueError:
messagebox.showwarning("提示", "数量必须是大于0的有效数字")
return
try:
datetime.strptime(new_ck_date, "%Y-%m-%d")
except ValueError:
messagebox.showwarning("日期错误", "打印日期格式应为 YYYY-MM-DD")
return
# ✅ 获取原记录信息
old_values = tree.item(selected[0])['values']
original_code, original_item = old_values[0], old_values[1]
old_qty = float(old_values[2])
# ✅ 校验客户料号
exists, _ = is_customer_ref_in_master(new_item)
if not exists:
messagebox.showerror(
"校验失败",
f"❌ 客户料号 '{new_item}' 不存在于主数据表中。\n"
f"无法保存修改。\n\n"
f"请确认输入是否正确。"
)
return
# ✅ 获取主料号
main_material_code = get_material_code_by_customer_ref(new_item)
if not main_material_code:
messagebox.showerror("映射失败", f"客户料号 '{new_item}' 未关联主数据料号。")
return
# ✅ 查询修改前的历史签收总量（排除当前这条）
current_signed_before = get_total_signed_for_item(original_item, exclude_ck_code=original_code)
total_shipped = get_total_outbound_quantity(main_material_code)
# 当前允许的最大签收量 = 总出库 - 历史签收
max_allowed_after = total_shipped - current_signed_before
if new_qty > max_allowed_after:
messagebox.showerror(
"超额签收",
f"❌ 修改后签收 {new_qty:g} 将超过允许上限 {max_allowed_after:g}！"
)
return
# ✅ 撤销旧签收的影响（LIFO 回滚）
if not revert_old_receipt(original_item, old_qty):
return # 错误已在函数内处理
# ✅ 应用新的签收（FIFO 更新）
if not update_outbound_receipt(main_material_code, new_qty):
return # 错误已在函数内处理
# ✅ 构建备注
unreceived_now = get_unreceived_quantity(main_material_code)
match_info = f"出库未签收匹配 {new_qty:g}/{unreceived_now + new_qty:g}"
updated_remark = f"{match_info}; {new_remark}" if new_remark.strip() else match_info
# ✅ 执行更新
cursor.execute("""
UPDATE CK_receipt
SET CK_Code=%s, CK_Item=%s, Qty=%s, Location=%s, CK_Date=%s, Ref=%s
WHERE CK_Code=%s AND CK_Item=%s
""", (
new_code, new_item, new_qty, new_loc, new_ck_date, updated_remark,
original_code, original_item
))
conn.commit()
messagebox.showinfo("成功", "✅ 修改已保存并同步更新签收状态")
edit_win.destroy()
refresh_list()
window.lift()
except Exception as e:
conn.rollback()
messagebox.showerror("错误", f"更新失败：\n{str(e)}")
# =============================
# ✅ 绑定回车键保存 & 取消按钮
# =============================
# 添加按钮框架
btn_frame = ttk.Frame(edit_win)
btn_frame.grid(row=len(display_fields), column=0, columnspan=2, pady=20)
ttk.Button(btn_frame, text="💾 保存", command=save_edit).pack(side="left", padx=10)
ttk.Button(btn_frame, text="❌ 取消", command=edit_win.destroy).pack(side="left")
# 回车绑定到保存
edit_win.bind("<Return>", lambda event: save_edit())
# ESC 关闭窗口
edit_win.bind("<Escape>", lambda event: edit_win.destroy())
# =============================
# ✅ 清空输入框
# =============================
def clear_inputs():
ck_code_var.set("")
ck_item_var.set("")
qty_var.set("")
location_var.set("")
ck_date_var.set("")
remark_var.set("")
# =============================
# ✅ 复制功能（所有人可用）
# =============================
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 import_from_excel(append_mode=False):
mode_text = "追加导入" if append_mode else "覆盖导入"
if not append_mode and not messagebox.askyesno("⚠️ 确认操作", f"此操作将清空当前所有客户签收数据，并用Excel中的数据完全替换。\n\n是否继续 {mode_text}？"):
return
filepath = filedialog.askopenfilename(filetypes=[("Excel 文件", "*.xlsx"), ("所有文件", "*.*")])
if not filepath:
return
try:
df = pd.read_excel(filepath)
required_columns = {'CK_Code', 'CK_Item', 'Qty', 'Location', 'CK_Date', 'Ref'}
missing = required_columns - set(df.columns)
if missing:
messagebox.showerror("格式错误", f"❌ 缺少必要列：{missing}")
return
# 数据清洗
df['CK_Code'] = df['CK_Code'].astype(str).str.strip()
df['CK_Item'] = df['CK_Item'].astype(str).str.strip()
df['Qty'] = pd.to_numeric(df['Qty'], errors='coerce').fillna(0)
df['Location'] = df['Location'].astype(str).str.strip()
df['CK_Date'] = df['CK_Date'].astype(str).str.strip()
df['Ref'] = df['Ref'].astype(str).str.strip()
valid_df = df.dropna(subset=['CK_Code', 'CK_Item']).query('CK_Code != "nan" and CK_Item != "nan"')
if valid_df.empty:
messagebox.showwarning("无有效数据", "Excel 中没有可导入的有效数据行。")
return
items_in_excel = set(valid_df['CK_Item'].unique())
# 校验物料主数据是否存在这些客户料号（可选）
if len(items_in_excel) > 0:
query = "SELECT material_code FROM material_master WHERE CustomerRef IN %s"
cursor.execute(query, (tuple(items_in_excel),))
existing_refs = {row[0] for row in cursor.fetchall()}
missing_refs = items_in_excel - existing_refs
if missing_refs:
sample = ", ".join(list(missing_refs)[:10])
if len(missing_refs) > 10: sample += "..."
messagebox.showwarning(
"校验提示",
f"以下【客户参考】不在主数据中：\n{sample}\n\n虽然允许录入，但建议核对一致性。"
)
# 开始导入
if not append_mode:
cursor.execute("DELETE FROM CK_receipt") # 覆盖模式先清空
inserted_count = 0
for _, row in valid_df.iterrows():
try:
register_date = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
cursor.execute("""
INSERT INTO CK_receipt (CK_Code, CK_Item, Qty, Location, CK_Date, Date, Ref)
VALUES (%s, %s, %s, %s, %s, %s, %s)
""", (row['CK_Code'], row['CK_Item'], row['Qty'], row['Location'], row['CK_Date'], register_date, row['Ref']))
inserted_count += 1
except Exception as e:
print(f"插入失败（忽略）: {e}")
conn.commit()
action = "追加" if append_mode else "覆盖"
messagebox.showinfo("导入成功", f"✅ 已{action}导入 {inserted_count} 条客户签收记录！")
refresh_list()
except Exception as e:
conn.rollback()
messagebox.showerror("导入失败", f"发生错误：\n{str(e)}")
# =============================
# ✅ 导出功能（所有人可用）
# =============================
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)}")
# =============================
# UI 布局（根据权限控制）
# =============================
# =============================
# UI 布局（重点修改 打印日期 行）
# =============================
input_frame = ttk.Frame(window)
input_frame.grid(row=0, rowspan=7, column=0, columnspan=2, padx=10, pady=10, sticky="nw")
# 登记号
ttk.Label(input_frame, text="登记号").grid(row=0, column=0, padx=10, pady=4, sticky='e')
ttk.Entry(input_frame, textvariable=ck_code_var, width=25).grid(row=0, column=1, padx=10, pady=4, sticky='w')
# 客户料号
ttk.Label(input_frame, text="客户料号").grid(row=1, column=0, padx=10, pady=4, sticky='e')
ttk.Entry(input_frame, textvariable=ck_item_var, width=25).grid(row=1, column=1, padx=10, pady=4, sticky='w')
# 数量
ttk.Label(input_frame, text="数量").grid(row=2, column=0, padx=10, pady=4, sticky='e')
ttk.Entry(input_frame, textvariable=qty_var, width=25).grid(row=2, column=1, padx=10, pady=4, sticky='w')
# 地点
ttk.Label(input_frame, text="地点").grid(row=3, column=0, padx=10, pady=4, sticky='e')
ttk.Entry(input_frame, textvariable=location_var, width=25).grid(row=3, column=1, padx=10, pady=4, sticky='w')
# 打印日期（带选择按钮）
ttk.Label(input_frame, text="打印日期").grid(row=4, column=0, padx=10, pady=4, sticky='e')
date_frame = ttk.Frame(input_frame)
date_frame.grid(row=4, 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))
# 添加失焦校验
def validate_date_blur(event=None):
val = ck_date_var.get().strip()
if not val: return
try:
dt = datetime.strptime(val, "%Y-%m-%d")
fmt = dt.strftime("%Y-%m-%d")
if val != fmt:
ck_date_var.set(fmt)
except ValueError:
messagebox.showwarning("格式错误", "日期格式应为 YYYY-MM-DD")
ck_date_var.set("")
entry_ck_date.focus_set()
entry_ck_date.bind("<FocusOut>", validate_date_blur)
# 备注
ttk.Label(input_frame, text="备注").grid(row=5, column=0, padx=10, pady=4, sticky='e')
ttk.Entry(input_frame, textvariable=remark_var, width=25).grid(row=5, 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')
# --- 按钮区 ---
button_frame = ttk.Frame(window)
button_frame.grid(row=7, column=0, columnspan=4, pady=10, sticky='w', padx=(10, 0))
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=lambda: import_from_excel(append_mode=False)).pack(side="left", padx=5)
#ttk.Button(button_frame, text="📥 追加导入", command=lambda: import_from_excel(append_mode=True)).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 event: context_menu.post(event.x_root, event.y_root))
# --- 双击编辑 ---
tree.bind("<Double-1>", on_double_click)
# --- 初始化数据 ---
refresh_list()
# --- 关闭事件 ---
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 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):
"""
检查客户料号（CustomerRef）是否存在于 material_master 表中
返回: (bool, all_refs) 其中 all_refs 用于后续做自动补全
"""
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):
"""
根据 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
############################################
def get_unreceived_quantity(material_code: str, exclude_ck_code: str = None) -> float:
"""
查询指定 material_code 的未签收总量。
可选排除某个 CK_Code（用于编辑场景）
"""
try:
# 总出库量
cursor.execute("""
SELECT COALESCE(SUM(quantity), 0)
FROM outbound
WHERE material_code = %s
""", (material_code,))
total_out = float(cursor.fetchone()[0])
# 已签收总量（来自 outbound 表的 receipt 字段）
cursor.execute("""
SELECT COALESCE(SUM(receipt), 0)
FROM outbound
WHERE material_code = %s
""", (material_code,))
total_received = float(cursor.fetchone()[0])
# 来自 CK_receipt 的额外签收（但要排除当前正在编辑的这条）
base_query = "SELECT COALESCE(SUM(Qty), 0) FROM CK_receipt WHERE CK_Item IN (SELECT CustomerRef FROM material_master WHERE material_code = %s)"
params = [material_code]
if exclude_ck_code:
base_query += " AND CK_Code != %s"
params.append(exclude_ck_code)
cursor.execute(base_query, tuple(params))
total_from_ck = float(cursor.fetchone()[0])
# 实际未签收 = 总出库 - outbound.receipt - 其他 CK_sign（不含当前）
unreceived = total_out - total_received - total_from_ck
return max(0.0, unreceived)
except Exception as e:
messagebox.showerror("查询失败", f"统计未签收数量失败：\n{e}")
return 0.0
##############################################
def update_outbound_receipt(material_code: str, add_qty: float) -> bool:
"""
给指定物料的所有出库记录按 FIFO（先进先出）策略更新 receipt。
从最早的一条开始填，直到用完 add_qty。
"""
if not conn or not cursor:
messagebox.showerror("错误", "数据库连接异常")
return False
try:
# 🔒 加锁防止并发冲突（InnoDB 支持）
select_query = """
SELECT ID, quantity, IFNULL(receipt, 0) AS receipt
FROM outbound
WHERE material_code = %s
AND IFNULL(receipt, 0) < quantity
ORDER BY date_out ASC, ID ASC
FOR UPDATE
"""
cursor.execute(select_query, (material_code,))
records = cursor.fetchall()
remaining = float(add_qty)
for record in records:
record_id, qty, received = record
qty, received = float(qty), float(received)
available = qty - received # 还能签收多少
if remaining <= 0:
break
take = min(available, remaining)
new_receipt = received + take
# ✅ 使用 ID 精确更新
update_query = "UPDATE outbound SET receipt = %s WHERE ID = %s"
cursor.execute(update_query, (new_receipt, record_id))
remaining -= take
# 可选：提示超额部分
if remaining > 0:
messagebox.showwarning(
"超额签收",
f"签收总量超出当前未签收记录 {remaining:g}，请检查是否遗漏出库数据。"
)
conn.commit()
return True
except Exception as e:
conn.rollback()
error_msg = str(e)
if "Unknown column 'receipt'" in error_msg:
error_msg = "缺少字段 'receipt'，请执行：\n" \
"ALTER TABLE outbound ADD COLUMN receipt FLOAT DEFAULT 0;"
messagebox.showerror("更新失败", f"更新签收状态失败：\n{error_msg}")
return False
##############################################
def revert_old_receipt(customer_ref: str, old_qty: float) -> bool:
"""
撤销某个客户料号的历史签收影响：将其对应的主料号出库记录中的 receipt 减去 old_qty
使用 LIFO 策略（最新优先回滚）
"""
try:
main_code = get_material_code_by_customer_ref(customer_ref)
if not main_code:
messagebox.showerror("映射失败", f"客户料号 '{customer_ref}' 无对应主料号")
return False
# 获取所有已签收的记录（倒序：最新优先）
cursor.execute("""
SELECT ID, receipt
FROM outbound
WHERE material_code = %s AND receipt > 0
ORDER BY date_out DESC, ID DESC
""", (main_code,))
records = cursor.fetchall()
to_revert = float(old_qty)
for record_id, current_receipt in records:
if to_revert <= 0:
break
can_reduce = min(float(current_receipt), to_revert)
new_receipt = float(current_receipt) - can_reduce
cursor.execute("UPDATE outbound SET receipt = %s WHERE ID = %s", (new_receipt, record_id))
to_revert -= can_reduce
conn.commit()
return True
except Exception as e:
conn.rollback()
messagebox.showerror("回滚失败", f"撤销旧签收失败：\n{e}")
return False
#################################################################
######################################################################
def get_total_signed_for_item(customer_ref: str, exclude_ck_code: str = None) -> float:
"""
获取指定客户料号（CustomerRef）已被签收的总数量
可选排除某个 CK_Code（用于编辑）
"""
try:
query = "SELECT COALESCE(SUM(Qty), 0) FROM CK_receipt WHERE CK_Item = %s"
params = [customer_ref]
if exclude_ck_code:
query += " AND CK_Code != %s"
params.append(exclude_ck_code)
cursor.execute(query, tuple(params))
return float(cursor.fetchone()[0])
except Exception as e:
print(f"统计历史签收失败: {e}")
return 0.0
###########################################
def get_total_outbound_quantity(material_code: str) -> float:
"""获取某物料的总出库数量"""
try:
cursor.execute("SELECT COALESCE(SUM(quantity), 0) FROM outbound WHERE material_code = %s", (material_code,))
return float(cursor.fetchone()[0])
except Exception as e:
print(f"查询总出库失败: {e}")
return 0.0
###################################################
def update_outbound_receipt_safe(material_code: str, add_qty: float, ck_code: str) -> bool:
"""
安全版本：只有当该 ck_code 尚未处理时才执行更新
避免重复扣减
"""
try:
# 先检查是否已处理过此 CK_Code
cursor.execute("SELECT COUNT(*) FROM CK_receipt WHERE CK_Code = %s AND processed = TRUE", (ck_code,))
if cursor.fetchone()[0] > 0:
print(f"⚠️ CK_Code {ck_code} 已处理，拒绝重复更新")
return False
# 开始事务
cursor.execute("""
SELECT ID, quantity, IFNULL(receipt, 0) AS receipt
FROM outbound
WHERE material_code = %s AND IFNULL(receipt, 0) < quantity
ORDER BY date_out ASC, ID ASC
FOR UPDATE
""", (material_code,))
records = cursor.fetchall()
remaining = float(add_qty)
updated_any = False
for record in records:
record_id, qty, received = record
qty, received = float(qty), float(received)
available = qty - received
if remaining <= 0:
break
take = min(available, remaining)
new_receipt = received + take
cursor.execute("UPDATE outbound SET receipt = %s WHERE ID = %s", (new_receipt, record_id))
remaining -= take
updated_any = True
if not updated_any and remaining == add_qty:
messagebox.showwarning("无变更", "未找到可更新的出库记录")
return False
# 标记 CK_receipt 为已处理
cursor.execute("UPDATE CK_receipt SET processed = TRUE WHERE CK_Code = %s", (ck_code,))
return True
except Exception as e:
print(f"更新签收失败: {e}")
messagebox.showerror("更新失败", f"更新签收状态失败：\n{e}")
return False
修改不需要每行记录已签收了，出库行也不需要标记。签收备注也不需要。需要在签收管理页面右上角小区域做新的list表区域，统计所有对应料号，客户料号的出库数量总数对比签收的总数。 可以根据时间区间筛选统计。
本文解答了 SAP 系统中 SD-MM 模块关于客户物料信息记录的用途及维护方式。通过事务代码VD51可以进行维护，用于链接公司内部物料名称与客户所用名称，确保发票上显示客户习惯的名称。此外，还可以设置默认工厂，优先级高于物料主数据中的设置。
扫一扫
1228
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
