174 lines
6.8 KiB
Python
174 lines
6.8 KiB
Python
#!/usr/bin/env python3
|
||
"""
|
||
第三轮修复：内料继承主产品供应商 + CSV中有但供应商为空的补充
|
||
规则：
|
||
1. 内料(-NL/【内料】): 去掉 -NL 后缀，找到主产品，继承主产品的供应商
|
||
2. CSV中有记录但供应商为空的包材: 去掉后缀找主产品继承
|
||
3. CSV中有记录且有供应商的：不再处理（v2已完成）
|
||
"""
|
||
import csv
|
||
import pymysql
|
||
from config import DB_CONFIG
|
||
|
||
CSV_FILE = "/home/gxd/.openclaw/workspace-dingtalk-assistant/media/inbound/采购订单-20260531-1780213945661.csv"
|
||
|
||
def strip_suffix(code):
|
||
"""去掉已知后缀返回base_code"""
|
||
SUFFIXES = [
|
||
'-FM/GZ', '-TMGJ-NL', '-ALJK-BT', '-ALJK-FM/GZ',
|
||
'-TM-BT', '-TM-PZ', '-TM-CH', '-TM-BQ', '-TM-SSM',
|
||
'-TM-HMT', '-TM-RG', '-TM-GZ', '-TM-FM/GZ',
|
||
'-PDD-BT', '-PDD-PZ', '-PDD-CH', '-PDD-HMT',
|
||
'-NL', '-BT', '-PZ', '-CH', '-BQ', '-SSM', '-HMT',
|
||
'-RG', '-GZ', '-JD', '-PDD', '-ALJK', '-TM',
|
||
'-WPH', '-DW',
|
||
]
|
||
for s in SUFFIXES:
|
||
if code.endswith(s):
|
||
base = code[:-len(s)]
|
||
if base:
|
||
return base
|
||
return code
|
||
|
||
def main():
|
||
conn = pymysql.connect(**DB_CONFIG)
|
||
cur = conn.cursor()
|
||
|
||
# 1. 读取CSV——构建精确匹配索引
|
||
csv_best = {}
|
||
with open(CSV_FILE, encoding="utf-8-sig") as f:
|
||
reader = csv.DictReader(f)
|
||
for row in reader:
|
||
code = row["物料编码"].strip()
|
||
supplier = row["供应商"].strip()
|
||
price_str = row["含税单价"].strip()
|
||
try:
|
||
price = float(price_str) if price_str else None
|
||
except ValueError:
|
||
price = None
|
||
if code not in csv_best:
|
||
csv_best[code] = (supplier, price)
|
||
else:
|
||
old_sup, old_price = csv_best[code]
|
||
if supplier and not old_sup:
|
||
csv_best[code] = (supplier, old_price if old_price is not None else price)
|
||
if old_price is None and price is not None:
|
||
csv_best[code] = (old_sup if old_sup else supplier, price)
|
||
|
||
print(f"CSV唯一物料编码: {len(csv_best)}")
|
||
|
||
# 2. 找出所有包材类且supplier_name_match为空的产品
|
||
keywords = ['瓶子','泵头','彩盒','海绵头','瓶贴','标签','收缩膜','瓶体','瓶标','软管','盖子','膜袋','标贴','唇膏管','内料','海绵','瓶子+内塞','泵头','瓶标','海绵头']
|
||
clauses = " OR ".join(["product_name LIKE '%%{}%%'".format(k) for k in keywords])
|
||
|
||
cur.execute("""
|
||
SELECT id, product_code, product_name, cost_price
|
||
FROM products
|
||
WHERE ({}) AND (supplier_name_match IS NULL OR supplier_name_match = '')
|
||
""".format(clauses))
|
||
need_fix = cur.fetchall()
|
||
print(f"需要修复的包材(当前supplier为空): {len(need_fix)}")
|
||
|
||
# 3. 加载所有产品的supplier_name_match（包括已有值的），用于继承
|
||
cur.execute("SELECT product_code, supplier_name_match, cost_price FROM products")
|
||
all_prod = {}
|
||
for code, sup, price in cur.fetchall():
|
||
all_prod[code.strip()] = {
|
||
'supplier': sup or '',
|
||
'cost_price': float(price) if price else 0
|
||
}
|
||
|
||
# 4. 逐个处理
|
||
update_1 = [] # 内料继承主产品供应商
|
||
update_2 = [] # CSV中供应商为空但可继承主产品的
|
||
update_3 = [] # 通过找主产品获得供应商（其他后缀）
|
||
|
||
for pid, product_code, product_name, old_price in need_fix:
|
||
code = product_code.strip()
|
||
is_nl = code.endswith('-NL') or '【内料】' in product_name
|
||
new_supplier = None
|
||
|
||
# 4a. 检查CSV是否有该编码且供应商不为空
|
||
if code in csv_best:
|
||
csv_sup, csv_price = csv_best[code]
|
||
if csv_sup:
|
||
# CSV有供应商——v2应该已经覆盖了，但如果还没覆盖到
|
||
new_supplier = csv_sup
|
||
new_price = csv_price if csv_price is not None else (float(old_price) if old_price else None)
|
||
update_2.append((new_supplier, new_price, pid))
|
||
continue
|
||
|
||
# 4b. 去掉后缀找主产品
|
||
base_code = strip_suffix(code)
|
||
if base_code and base_code != code and base_code in all_prod:
|
||
base = all_prod[base_code]
|
||
if base['supplier']:
|
||
new_supplier = base['supplier']
|
||
|
||
# 4c. 对于内料，特别处理：多剥一层后缀
|
||
if not new_supplier and is_nl:
|
||
# 去-NL后缀后再去一次
|
||
stripped = code
|
||
if stripped.endswith('-NL'):
|
||
stripped = stripped[:-3]
|
||
# 再去其他后缀
|
||
base_code2 = strip_suffix(stripped)
|
||
if base_code2 and base_code2 in all_prod:
|
||
base = all_prod[base_code2]
|
||
if base['supplier']:
|
||
new_supplier = base['supplier']
|
||
|
||
if new_supplier:
|
||
new_price = float(old_price) if old_price else None
|
||
if is_nl:
|
||
update_1.append((new_supplier, new_price, pid))
|
||
else:
|
||
update_2.append((new_supplier, new_price, pid))
|
||
|
||
print(f"\n内料继承主产品供应商: {len(update_1)}")
|
||
print(f"其他后缀继承主产品供应商: {len(update_2)}")
|
||
print(f"仍然无法匹配: {len(need_fix) - len(update_1) - len(update_2)}")
|
||
|
||
# 5. 批量更新
|
||
def run_updates(label, updates_data):
|
||
if not updates_data:
|
||
return
|
||
sql = "UPDATE products SET supplier_name_match=%s, cost_price=%s WHERE id=%s"
|
||
n = 0
|
||
for i in range(0, len(updates_data), 500):
|
||
batch = updates_data[i:i+500]
|
||
cur.executemany(sql, batch)
|
||
conn.commit()
|
||
n += len(batch)
|
||
print(f" {label}: {n} 条已更新")
|
||
|
||
run_updates("内料继承", update_1)
|
||
run_updates("其他继承", update_2)
|
||
|
||
# 6. 最终统计
|
||
cur.execute("SELECT COUNT(*) FROM products WHERE ({}) AND (supplier_name_match IS NULL OR supplier_name_match = '')".format(clauses))
|
||
still_empty = cur.fetchone()[0]
|
||
print(f"\n修复后，包材仍然为空: {still_empty}")
|
||
|
||
# 按后缀分类查看剩余为空的情况
|
||
if still_empty > 0:
|
||
cur.execute("""
|
||
SELECT product_code, product_name FROM products
|
||
WHERE ({}) AND (supplier_name_match IS NULL OR supplier_name_match = '')
|
||
""".format(clauses))
|
||
remain = cur.fetchall()
|
||
nl_left = sum(1 for c, _ in remain if c.endswith('-NL'))
|
||
print(f" 其中-NL内料: {nl_left}")
|
||
print(f" 其他: {still_empty - nl_left}")
|
||
|
||
cur.execute("SELECT COUNT(*) FROM products WHERE supplier_name_match != ''")
|
||
total_sup = cur.fetchone()[0]
|
||
print(f"\n全部产品有供应商: {total_sup}")
|
||
|
||
conn.close()
|
||
print("\n✅ 完成！")
|
||
|
||
|
||
if __name__ == "__main__":
|
||
main()
|