1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| import json
| from pathlib import Path
| P=Path('scripts/bom-import')
| def sqlstr(s):return 'CONVERT(0x'+str(s).encode().hex()+' USING utf8mb4)' if str(s) else "''"
| for target in ['local','remote']:
| plan=json.loads((P/f'{target}-plan.json').read_text(encoding='utf-8'))
| sql=['CREATE TEMPORARY TABLE `mom-hm`.stage_bom_import(parent_id BIGINT,child_id BIGINT,parent_code VARCHAR(50),child_code VARCHAR(50),parent_name VARCHAR(200),child_name VARCHAR(200),parent_unit BIGINT,child_unit BIGINT,quantity DECIMAL(24,6),remark VARCHAR(255),action INT,existing_id BIGINT,old_quantity DECIMAL(24,6),PRIMARY KEY(parent_id,child_id)) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci']
| vals=[]
| for x in plan['lines']:
| remark=f"来源：{x['file']}/{x['sheet']}/第{x['row']}行；原始用量{x['quantity']} {x['unit']}"
| if x['section']:remark+='；下级组成：'+x['section']
| if x['factor']!='1':remark+='；已按系统单位换算'
| assert len(remark)<=255
| action={'新增':0,'更新':1,'已存在':2}[x['action']]
| values=[str(x['parent_id']),str(x['child_id']),sqlstr(x['parent_code']),sqlstr(x['child_code']),sqlstr(x['parent_name']),sqlstr(x['child_name']),x['parent_unit_id'],x['child_unit_id'],x['system_quantity'],sqlstr(remark),str(action),str(x.get('existing_id',0)),str(x.get('old_quantity',0))]
| vals.append('('+','.join(values)+')')
| for i in range(0,len(vals),100):sql.append('INSERT INTO `mom-hm`.stage_bom_import VALUES '+','.join(vals[i:i+100]))
| (P/f'{target}-import.sql').write_text(';\n'.join(sql)+';\n',encoding='utf-8')
|
|
