252 行
14 KiB
Python
252 行
14 KiB
Python
import json, re, math
|
||
from openpyxl import Workbook
|
||
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
|
||
from openpyxl.utils import get_column_letter
|
||
|
||
import os
|
||
T = os.path.dirname(os.path.abspath(__file__)) + '/'
|
||
L = json.load(open(T + 'bom_agg.json', encoding='utf-8'))
|
||
TB = json.load(open(T + 'tb.json', encoding='utf-8'))
|
||
LC = json.load(open(T + 'lcsc_cny.json', encoding='utf-8'))
|
||
Q = {x['sid']: x for x in json.load(open(T + 'queries.json', encoding='utf-8'))}
|
||
SETS = 1 # 1 底板 + 5 刀片 = 1 套
|
||
|
||
# 非阻容料：标题里必须出现的型号核心（去掉空格/横杠后大写匹配）
|
||
CORE = {
|
||
'C382328': 'AO4407', 'C163651': 'SMBJ13A', 'C2887585': 'TL431', 'C49851': 'INA226',
|
||
'C5210749': 'LMR51430', 'C38117': 'SWPA4030S100', 'C76857': 'SPM6530T4R7', 'C2913202': 'ESP32S3WROOM1N16R8',
|
||
'C50951': '', 'C130026': 'TCA9548', 'C5215473': 'DAC63204', 'C478081': 'USB2517', 'C2762192': '24M',
|
||
'C165948': 'TYPEC31M12', 'C6186': 'AMS1117', 'C116575': 'T491A226', 'C19188869': 'PCIE',
|
||
'C20917': 'AO3400', 'C2891776': 'BZT52C8V2', 'C15127': 'AO3401', 'C8678': 'SS34', 'C2847671': '2510',
|
||
'C13564': 'NCP18XH103', 'C310990': 'F1206HC12', 'C151920': 'SMBJ22A', 'C42459160': 'CH224',
|
||
'C3188410': 'LM61495', 'C780205': 'SRP1265A4R7', 'C84248': 'TPS54622', 'C2653873': 'TPS25940',
|
||
'C8545': '2N7002', 'C310998': 'F1206SB2', 'C2688893': 'PCA9555', 'C225104': 'A1251', 'C353034': 'TS5A3157',
|
||
'C3828985': '2200', 'C5221288': 'YTC1P', 'C424561': 'BM2450DS', 'C2687116': 'USBLC62', 'C131941': 'TPS22918',
|
||
'C841779': '5MR', 'C22383480': '470UF',
|
||
}
|
||
|
||
CORE['C5210749'] = 'LMR51430YDDC'
|
||
# 封装必须对（标题里常把同型号不同封装混卖）
|
||
PKG = {'C2887585': 'SOT23', 'C20917': 'SOT23', 'C15127': 'SOT23', 'C8545': 'SOT23',
|
||
'C382328': 'SOP8|SOIC8', 'C6186': 'SOT223', 'C8678': 'SMA|DO214AC', 'C2891776': 'SOD123'}
|
||
|
||
import re as _re
|
||
SPECIAL = {
|
||
'C19188869': lambda U, t: '64P' in U and _re.search(r'X4|4X', U) and _re.search(r'贴片|SMT', U) and '164' not in U,
|
||
'C5221288': lambda U, t: _re.search(r'贴片|SMT', U) and _re.search(r'POGO|顶针|弹簧针', U) and not _re.search(r'夹|间距|烧录', U),
|
||
'C116575': lambda U, t: _re.search(r'22UF|226', U) and '10V' in U and _re.search(r'A型|3216', U),
|
||
'C310998': lambda U, t: _re.search(r'(?<![\d.])2A', U) and '慢' in U and '1206' in U,
|
||
'C2762192': lambda U, t: '24M' in U and '3225' in U and _re.search(r'(?<!\d)9PF|负载9/', U) and '有源' not in U,
|
||
'C3828985': lambda U, t: '2200UF' in t and '63V' in t and _re.search(r'10X105|6\.3TPV', t + U),
|
||
'C225104': lambda U, t: 'A1251WV' in t,
|
||
}
|
||
E24 = [1.0, 1.1, 1.2, 1.3, 1.5, 1.6, 1.8, 2.0, 2.2, 2.4, 2.7, 3.0, 3.3, 3.6, 3.9, 4.3, 4.7, 5.1, 5.6, 6.2, 6.8, 7.5, 8.2, 9.1]
|
||
GENERIC = {'C5221288': '通用顶针，须按 Ø2mm / 高 3.5mm / 贴片 在规格里核对',
|
||
'C19188869': '非原厂同款（原设计 SOFNG PCIE-64P11L，板上高 11.1mm 带定位柱），下单前核对尺寸/定位柱',
|
||
'C2913202': '2026-09-13 定为 N16R8；多规格链接要选 N16R8 这一项',
|
||
'C3828985': '原设计为 Rubycon 6.3TPV 固态/高分子，替代品需核对 ESR≤60mΩ、纹波电流',
|
||
'C2762192': '原设计 NDK ±10ppm 9pF；替代品须 9pF 负载电容'}
|
||
|
||
def norm(s):
|
||
return re.sub(r'[\s\-_/·.,，()（）]', '', s.upper())
|
||
|
||
def cap_tokens(desc):
|
||
v = desc.split()[0] # e.g. 100nF
|
||
m = re.match(r'([\d.]+)(pF|nF|uF)', v)
|
||
num, u = float(m.group(1)), m.group(2)
|
||
pf = num * {'pF': 1, 'nF': 1e3, 'uF': 1e6}[u]
|
||
toks = {v.upper()}
|
||
# 三位码
|
||
e = int(math.floor(math.log10(pf))) - 1
|
||
code = f"{int(round(pf / 10**e)):02d}{e}" if e >= 0 else None
|
||
if code: toks.add(code)
|
||
if pf >= 1e3: toks.add(f"{pf/1e3:g}NF")
|
||
if pf >= 1e4: toks.add(f"{pf/1e6:g}UF")
|
||
return toks
|
||
|
||
def relevant(sid, r, it):
|
||
t = norm(it['title'] + ' ' + it['tags'])
|
||
q = Q[sid]['q']
|
||
U = (it['title'] + ' ' + it['tags']).upper()
|
||
if sid in SPECIAL:
|
||
return SPECIAL[sid](U, t)
|
||
if sid in CORE:
|
||
c = CORE[sid]
|
||
if sid == 'C50951':
|
||
return '2.54' in it['title'] and '排针' in it['title']
|
||
if sid == 'C841779':
|
||
return bool(re.search(r'(?<![\d.])(5MR|R005|0\.005R|5毫欧)', it['title'].upper()))
|
||
if sid in PKG and not re.search(PKG[sid], t):
|
||
return False
|
||
return c in t
|
||
fp = r['fp'] or ''
|
||
pk = re.search(r'(0402|0603|0805|1206|2512)', fp).group(1)
|
||
if pk not in t:
|
||
return False
|
||
if '贴片电阻' in q:
|
||
raw = (it['title'] + ' ' + it['tags']).upper().replace('Ω', '').replace('欧', '')
|
||
if '热敏' in raw or '排阻' in raw or '采样' in raw and pk != '2512':
|
||
return False
|
||
v = q.split()[1].upper() # 100K / 43.2K / 2R
|
||
m = re.match(r'([\d.]+)([KMR])', v); num, u = m.group(1), m.group(2)
|
||
forms = {v, num + u}
|
||
if '.' in num:
|
||
a, b = num.split('.'); forms.add(f'{a}{u}{b}')
|
||
mult = {'R': 1, 'K': 1e3, 'M': 1e6}[u]
|
||
ohm = float(num) * mult
|
||
e = int(math.floor(math.log10(ohm))) - 2
|
||
if e >= 0: forms.add(f"{int(round(ohm / 10**e)):03d}{e}")
|
||
if u == 'R': forms |= {num + 'R0', num + 'R00'}
|
||
if any(re.search(r'(?<![\d.])' + re.escape(f) + r'(?![\d])', raw) for f in forms):
|
||
return True
|
||
mant = ohm / 10 ** math.floor(math.log10(ohm))
|
||
if any(abs(mant - e) < 1e-6 for e in E24) and re.search(r'全系列|系列|全阻值|多阻值|阻值齐全', it['title']):
|
||
it['series'] = True
|
||
return True
|
||
return False
|
||
if '贴片电容' in q:
|
||
raw = (it['title'] + ' ' + it['tags']).upper().replace(' ', '')
|
||
volt = re.search(r'(\d+(\.\d)?V)', r['desc']).group(1).upper()
|
||
if not re.search(r'(?<![\d.])' + re.escape(volt), raw):
|
||
return False
|
||
return any(re.search(r'(?<![\d.])' + re.escape(k) + r'(?![\d])', raw) for k in cap_tokens(r['desc']))
|
||
return True
|
||
|
||
def sales_n(s):
|
||
m = re.search(r'([\d.]+)(万)?\+?人', s)
|
||
if not m: return 0
|
||
return float(m.group(1)) * (10000 if m.group(2) else 1)
|
||
|
||
def pack_of(title):
|
||
m = re.search(r'[（(]?(\d+)\s*(只|个|颗|片|pcs|PCS)[）)]?', title)
|
||
return int(m.group(1)) if m and int(m.group(1)) > 1 else None
|
||
|
||
def lcsc_price(sid, qty):
|
||
v = LC.get(sid) or {}
|
||
ps = v.get('prices') or []
|
||
best = None
|
||
for p in ps:
|
||
if qty >= p['ladder'] or best is None:
|
||
best = p
|
||
return (best['currencyPrice'], best['ladder']) if best else (None, None)
|
||
|
||
rows = []
|
||
for r in L:
|
||
sid = r['sid'] or ''
|
||
if not sid.startswith('C'):
|
||
continue
|
||
items = [i for i in TB.get(sid, {}).get('items', []) if i['price']]
|
||
rel = [i for i in items if relevant(sid, r, i)]
|
||
for i in rel:
|
||
i['p'] = float(i['price']); i['s'] = sales_n(i['sales']); i['pack'] = pack_of(i['title'])
|
||
# 选店：先按销量挑，同店只取一条，再补最低价
|
||
picks, shops = [], set()
|
||
for i in sorted(rel, key=lambda i: (i.get('series', False), -i['s'])):
|
||
if i['shop'] in shops: continue
|
||
picks.append(i); shops.add(i['shop'])
|
||
if len(picks) == 3: break
|
||
cheap = [i for i in sorted(rel, key=lambda i: i['p']) if i['shop'] not in shops and i['s'] > 0]
|
||
if cheap: picks.append(cheap[0])
|
||
picks.sort(key=lambda i: i['p'])
|
||
lp, ll = lcsc_price(sid, r['total'])
|
||
rows.append(dict(r=r, picks=picks, nrel=len(rel), nall=len(items), lp=lp, ll=ll))
|
||
|
||
# ---------- Excel ----------
|
||
wb = Workbook()
|
||
ws = wb.active; ws.title = 'BOM+淘宝比价'
|
||
hdr = ['#', '分类', '型号', '厂商', '立创料号', '规格', '封装', '底板位号', '刀片位号(每片)', '底板数', '刀片每片数', '总数(1底板+5刀片)',
|
||
'立创单价¥', '立创价阶梯', '立创小计¥', '立创链接', '淘宝搜索词', '淘宝搜索链接', '备注']
|
||
for k in range(1, 5):
|
||
hdr += [f'店{k} 店铺', f'店{k} 列表价¥', f'店{k} 付款人数', f'店{k} 标题', f'店{k} 链接']
|
||
ws.append(hdr)
|
||
|
||
def cat(r):
|
||
m, fp = r['mid'] or '', r['fp'] or ''
|
||
if re.match(r'0402WGF|RHC|FRC|HoGT|NCP18', m): return '电阻'
|
||
if re.match(r'CL\d|GRM|0402CG|T491|470uF|6.3TPV', m): return '电容'
|
||
if re.match(r'SPM|SWPA|SRP', m): return '电感'
|
||
if re.match(r'AO|2N7|SS34|SMBJ|BZT|USBLC', m): return '分立/保护'
|
||
if re.match(r'F1206', m): return '保险丝'
|
||
if re.search(r'TYPE-C|PCIE|A1251|2510|BM24|YTC|排针', m): return '连接器'
|
||
if 'MHZ' in m: return '晶振'
|
||
return 'IC/模组'
|
||
|
||
ORDER = ['IC/模组', '电感', '分立/保护', '保险丝', '晶振', '连接器', '电容', '电阻']
|
||
rows.sort(key=lambda x: (ORDER.index(cat(x['r'])), x['r']['mid']))
|
||
total_lcsc = 0
|
||
def note(x):
|
||
r = x['r']; sid = r['sid']; ns = []
|
||
if sid in GENERIC: ns.append(GENERIC[sid])
|
||
if not x['picks']: ns.append('淘宝没搜到可对应的链接')
|
||
c = cat(r)
|
||
if x['lp'] and x['picks'] and c in ('IC/模组',) and min(i['p'] for i in x['picks']) < 0.3 * x['lp']:
|
||
ns.append('淘宝最低价不到立创的 30%，警惕散新/翻新/引流价')
|
||
if not x['lp']: ns.append('立创接口无报价（料号下架或非立创自营）')
|
||
return '；'.join(ns)
|
||
for n, x in enumerate(rows, 1):
|
||
r = x['r']; sid = r['sid']; pid = (LC.get(sid) or {}).get('pid')
|
||
sub = round(x['lp'] * r['total'], 2) if x['lp'] else None
|
||
total_lcsc += sub or 0
|
||
q = Q[sid]['q']
|
||
row = [n, cat(r), r['mid'], r['m'], sid, r['desc'], r['fp'], ','.join(r['base']), ','.join(r['blade']),
|
||
len(r['base']), len(r['blade']), r['total'], x['lp'], f"≥{x['ll']}" if x['ll'] else '', sub,
|
||
f"https://item.szlcsc.com/{pid}.html" if pid else '', q,
|
||
'https://s.taobao.com/search?q=' + __import__('urllib.parse').parse.quote(q), note(x)]
|
||
for i in x['picks']:
|
||
url = ('https://detail.tmall.com/item.htm?id=' if i['tmall'] else 'https://item.taobao.com/item.htm?id=') + i['id']
|
||
row += [i['shop'], i['p'], i['sales'], ('【系列链接，需在规格里选阻值】' if i.get('series') else '') + i['title'], url]
|
||
ws.append(row)
|
||
|
||
thin = Side(style='thin', color='CCCCCC')
|
||
for c in ws[1]:
|
||
c.font = Font(bold=True, color='FFFFFF'); c.fill = PatternFill('solid', fgColor='305496')
|
||
c.alignment = Alignment(wrap_text=True, vertical='center')
|
||
widths = {19: 30, 1: 4, 2: 9, 3: 22, 4: 14, 5: 11, 6: 26, 7: 18, 8: 18, 9: 18, 10: 6, 11: 6, 12: 8, 13: 9, 14: 8, 15: 9, 16: 12, 17: 22, 18: 12}
|
||
for col in range(1, ws.max_column + 1):
|
||
w = widths.get(col)
|
||
if w is None:
|
||
w = {1: 14, 2: 9, 3: 9, 4: 36, 5: 10}[(col - 20) % 5 + 1]
|
||
ws.column_dimensions[get_column_letter(col)].width = w
|
||
for row in ws.iter_rows(min_row=2):
|
||
for c in row:
|
||
c.alignment = Alignment(vertical='top', wrap_text=False)
|
||
if isinstance(c.value, str) and c.value.startswith('https://'):
|
||
c.hyperlink = c.value; c.value = '打开'; c.font = Font(color='0563C1', underline='single')
|
||
ws.freeze_panes = 'D2'
|
||
ws.auto_filter.ref = ws.dimensions
|
||
ws.append([])
|
||
ws.append(['', '', '立创参考总价（按总数取对应阶梯价）', '', '', '', '', '', '', '', '', '', '', '', round(total_lcsc, 2)])
|
||
|
||
# 说明页
|
||
ns = wb.create_sheet('说明')
|
||
notes = [
|
||
'数据来源',
|
||
f'· 器件清单：从 EasyEDA 工程 PhoneRail-R2 的 13 页原理图逐页读出（底板 8 页 + 刀片 5 页），按立创料号合并。',
|
||
'· 数量按「1 块底板 + 5 片刀片」一套计算；不含 J1 金手指（板边铜箔，不采购）和 J5 电池排线焊盘（非器件）。',
|
||
'· 立创价：LCSC 官方接口的人民币报价，按本套总数取阶梯价；立创商城国内站与此可能略有出入。',
|
||
'· 淘宝：登录后按搜索词逐个搜索（每次间隔 30 秒），取前 24 条结果；按标题匹配型号/阻值容值/封装过滤掉不相关的，',
|
||
' 再挑销量最高的 3 家不同店铺 + 1 家最低价店铺，按价格排序。',
|
||
'',
|
||
'⚠ 看价格前必读',
|
||
'· 淘宝「列表价」是该链接所有 SKU 里最低的那一个，不一定是你要的型号/阻值/数量档；多规格链接（如阻值全系列）尤其如此。',
|
||
'· 阻容类淘宝通常按包（50/100 只）卖，列表价是一包的价格，不是单颗价；立创价是单颗价。两者不能直接相除比较。',
|
||
'· IC 类如果淘宝价明显低于立创价（比如低到 1/3 以下），多为拆机、翻新、散新或引流价，关键器件（TPS54622、TPS25940、',
|
||
' INA226、USB2517、DAC63204、ESP32 模组）建议走立创或品牌/授权店。',
|
||
'· 淘宝结果随时间、账号和地区变化；链接在抓取时有效。',
|
||
]
|
||
for s in notes:
|
||
ns.append([s])
|
||
ns.column_dimensions['A'].width = 120
|
||
ns['A1'].font = Font(bold=True); ns['A8'].font = Font(bold=True, color='C00000')
|
||
wb.move_sheet('说明', offset=-1)
|
||
out = 'D:/DCDC/BOM/PhoneRail-R2_BOM_淘宝比价.xlsx'
|
||
import os; os.makedirs('D:/DCDC/BOM', exist_ok=True)
|
||
wb.save(out)
|
||
json.dump([{'mid': x['r']['mid'], 'sid': x['r']['sid'], 'cat': cat(x['r']), 'total': x['r']['total'], 'lp': x['lp'], 'nrel': x['nrel'], 'nall': x['nall'],
|
||
'picks': [{k: i[k] for k in ('shop', 'p', 'sales', 'title', 'id', 'tmall')} for i in x['picks']]} for x in rows],
|
||
open(T + 'rows.json', 'w', encoding='utf-8'), ensure_ascii=False, indent=1)
|
||
print(out, len(rows), 'lcsc total', round(total_lcsc, 2))
|
||
for x in rows:
|
||
if len(x['picks']) < 2:
|
||
print('FEW', x['r']['sid'], x['r']['mid'], x['nrel'], '/', x['nall'])
|