139 lines
4.9 KiB
Python
139 lines
4.9 KiB
Python
#!/usr/bin/env python3
|
||
"""
|
||
BOM 成本价计算
|
||
规则：
|
||
1. 内料物料成本价 = 对应成品当前成本价
|
||
2. 成品成本价 = 该成品BOM下所有物料成本价×数量 之和
|
||
|
||
注意：规则1和2存在循环依赖（内料成本=成品成本=Σ所有物料，含内料自身）
|
||
解决方案：分两轮计算
|
||
第一轮：内料继承成品当前成本价（来自采购单导入的数据）
|
||
第二轮：成品成本 = SUM(物料成本×数量) —— 已包含内料继承的值
|
||
"""
|
||
from config import DB_CONFIG
|
||
import pymysql
|
||
from collections import defaultdict
|
||
|
||
def main():
|
||
conn = pymysql.connect(**DB_CONFIG)
|
||
cur = conn.cursor()
|
||
|
||
# 1. 获取BOM所有产品和物料关系
|
||
cur.execute("""
|
||
SELECT b.product_code, b.material_code, b.quantity,
|
||
m.cost_price AS material_cost,
|
||
p.cost_price AS product_cost
|
||
FROM bom b
|
||
LEFT JOIN products m ON b.material_code = m.product_code
|
||
LEFT JOIN products p ON b.product_code = p.product_code
|
||
ORDER BY b.product_code, b.material_code
|
||
""")
|
||
|
||
bom_data = defaultdict(list)
|
||
product_costs = {} # product_code -> current cost_price
|
||
material_costs = {} # material_code -> current cost_price
|
||
|
||
for row in cur.fetchall():
|
||
prod_code, mat_code, qty, mat_cost, prod_cost = row
|
||
bom_data[prod_code].append({
|
||
'material_code': mat_code,
|
||
'quantity': float(qty) if qty else 1,
|
||
'material_cost': float(mat_cost) if mat_cost else 0,
|
||
})
|
||
if prod_code not in product_costs:
|
||
product_costs[prod_code] = float(prod_cost) if prod_cost else 0
|
||
if mat_code not in material_costs:
|
||
material_costs[mat_code] = float(mat_cost) if mat_cost else 0
|
||
|
||
print(f"BOM 成品总数: {len(bom_data)}")
|
||
print(f"涉及物料编码数: {len(material_costs)}")
|
||
|
||
# 2. 找出所有内料物料
|
||
cur.execute("""
|
||
SELECT product_code, material_code, material_name
|
||
FROM bom
|
||
WHERE (material_name LIKE '%内料%' OR material_code LIKE '%-NL')
|
||
""")
|
||
nl_materials = []
|
||
for row in cur.fetchall():
|
||
nl_materials.append({
|
||
'product_code': row[0],
|
||
'material_code': row[1],
|
||
'material_name': row[2],
|
||
})
|
||
print(f"内料记录数: {len(nl_materials)}")
|
||
|
||
# 找出所有内料唯一编码
|
||
nl_codes = set(m['material_code'] for m in nl_materials)
|
||
print(f"内料唯一编码: {len(nl_codes)}")
|
||
|
||
# 3. 第一轮：内料继承成品当前的 cost_price
|
||
nl_updates = [] # (cost_price, material_code)
|
||
for m in nl_materials:
|
||
parent_code = m['product_code']
|
||
nl_code = m['material_code']
|
||
parent_cost = product_costs.get(parent_code, 0)
|
||
if parent_cost > 0:
|
||
nl_updates.append((parent_cost, nl_code))
|
||
|
||
print(f"\n===== 第一轮：内料继承成品成本 =====")
|
||
print(f"待更新内料: {len(nl_updates)}")
|
||
|
||
update_sql = "UPDATE products SET cost_price=%s WHERE product_code=%s"
|
||
updated = 0
|
||
if nl_updates:
|
||
cur.executemany(update_sql, nl_updates)
|
||
conn.commit()
|
||
updated = len(nl_updates)
|
||
print(f"已更新: {updated}")
|
||
|
||
# 4. 重新加载所有物料成本（包含刚刚更新的内料）
|
||
cur.execute("SELECT product_code, cost_price FROM products")
|
||
all_costs = {}
|
||
for code, price in cur.fetchall():
|
||
if code:
|
||
all_costs[code.strip()] = float(price) if price else 0
|
||
|
||
# 5. 第二轮：计算成品成本 = Σ(物料成本×数量)
|
||
prod_updates = {}
|
||
for prod_code, materials in bom_data.items():
|
||
total = 0
|
||
for m in materials:
|
||
mc = m['material_code'].strip()
|
||
mat_price = all_costs.get(mc, 0)
|
||
total += mat_price * m['quantity']
|
||
if total > 0:
|
||
prod_updates[prod_code] = round(total, 4)
|
||
|
||
print(f"\n===== 第二轮：成品成本 = Σ物料成本 =====")
|
||
print(f"待更新成品: {len(prod_updates)}")
|
||
|
||
if prod_updates:
|
||
update_data = [(cost, code) for code, cost in prod_updates.items()]
|
||
cur.executemany(update_sql, update_data)
|
||
conn.commit()
|
||
updated = len(update_data)
|
||
print(f"已更新: {updated}")
|
||
|
||
# 6. 对比效果
|
||
print(f"\n===== 更新前后对比（样举例） =====")
|
||
sample_codes = list(prod_updates.keys())[:8]
|
||
for code in sample_codes:
|
||
new_cost = prod_updates.get(code, 0)
|
||
print(f" {code:35s} → BOM计算成本: ¥{new_cost:.2f}")
|
||
|
||
# 7. 检查有多少成品成本变了
|
||
changed = 0
|
||
for code, new_cost in prod_updates.items():
|
||
old_cost = product_costs.get(code, 0)
|
||
if abs(new_cost - old_cost) > 0.001:
|
||
changed += 1
|
||
print(f"\n成本发生变化的成品数: {changed}/{len(prod_updates)}")
|
||
|
||
conn.close()
|
||
print("\n✅ 完成！")
|
||
|
||
|
||
if __name__ == "__main__":
|
||
main()
|