157 lines
5.6 KiB
Python
157 lines
5.6 KiB
Python
#!/usr/bin/env python3
|
||
"""
|
||
增量修复：只补当前 supplier_name_match 为空的包材产品
|
||
从采购订单CSV匹配供应商和成本价（取首个非空值）
|
||
"""
|
||
import csv
|
||
import pymysql
|
||
from config import DB_CONFIG
|
||
|
||
CSV_FILE = "/home/gxd/.openclaw/workspace-dingtalk-assistant/media/inbound/采购订单-20260531-1780213945661.csv"
|
||
|
||
# 后缀列表
|
||
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',
|
||
]
|
||
|
||
def strip_suffix(code):
|
||
code = code.strip()
|
||
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. 构建 CSV 索引：精确匹配 + 后缀剥离匹配
|
||
csv_exact = {} # code -> (supplier, price)
|
||
csv_base = {} # base_code -> (supplier, price)
|
||
for code, (sup, price) in csv_best.items():
|
||
csv_exact[code] = (sup, price)
|
||
base = strip_suffix(code)
|
||
# 只当 base != code 且 base 不在精确索引中时才加
|
||
if base != code and base not in csv_exact:
|
||
if base not in csv_base:
|
||
csv_base[base] = (sup, price)
|
||
else:
|
||
# 优先取非空供应商
|
||
old_sup, old_price = csv_base[base]
|
||
if sup and not old_sup:
|
||
csv_base[base] = (sup, old_price if old_price is not None else price)
|
||
|
||
# 3. 找出需要修复的包材产品——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"需要修复的包材: {len(need_fix)}")
|
||
|
||
# 4. 逐个匹配
|
||
updates = []
|
||
exact_hits = 0
|
||
base_hits = 0
|
||
no_hits = 0
|
||
|
||
for pid, product_code, product_name, old_price in need_fix:
|
||
code = product_code.strip()
|
||
sup = None
|
||
price = None
|
||
|
||
# 4a. 精确匹配
|
||
if code in csv_exact:
|
||
sup_s, price_s = csv_exact[code]
|
||
if sup_s:
|
||
sup = sup_s
|
||
if price_s is not None:
|
||
price = price_s
|
||
exact_hits += 1
|
||
# 4b. 后缀剥离匹配
|
||
elif code in csv_base:
|
||
sup_s, price_s = csv_base[code]
|
||
if sup_s:
|
||
sup = sup_s
|
||
if price_s is not None:
|
||
price = price_s
|
||
base_hits += 1
|
||
else:
|
||
no_hits += 1
|
||
|
||
if sup or price is not None:
|
||
# 保留原有价格当新价格不可用时
|
||
final_price = price if price is not None else (float(old_price) if old_price else None)
|
||
updates.append((sup or '', final_price, pid))
|
||
|
||
print(f"\n匹配结果: 精确={exact_hits}, 后缀={base_hits}, 未匹配={no_hits}")
|
||
print(f"待更新: {len(updates)}")
|
||
|
||
# 5. 批量更新
|
||
if updates:
|
||
update_sql = "UPDATE products SET supplier_name_match=%s, cost_price=%s WHERE id=%s"
|
||
updated = 0
|
||
batch_size = 500
|
||
for i in range(0, len(updates), batch_size):
|
||
batch = updates[i:i+batch_size]
|
||
cur.executemany(update_sql, batch)
|
||
conn.commit()
|
||
updated += len(batch)
|
||
print(f" 已更新 {updated}/{len(updates)}")
|
||
|
||
# 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}")
|
||
|
||
cur.execute("SELECT COUNT(*) FROM products WHERE supplier_name_match != ''")
|
||
total_sup = cur.fetchone()[0]
|
||
cur.execute("SELECT COUNT(*) FROM products WHERE cost_price > 0")
|
||
total_price = cur.fetchone()[0]
|
||
print(f"全部产品: 有供应商={total_sup}, 有成本价={total_price}")
|
||
|
||
conn.close()
|
||
print("\n✅ 完成！")
|
||
|
||
|
||
if __name__ == "__main__":
|
||
main()
|