1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
| import sys,json,re
| from pathlib import Path
| P=Path(__file__).parent
| sys.path.insert(0,str(P.parent/'iqc-import-20261012/deps'))
| import pymysql
| c=pymysql.connect(host='36.213.150.237',port=10000,user='root',password=sys.stdin.readline().strip(),database='mom-hm',charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor,read_timeout=60)
| s={'tables':{}}
| with c.cursor() as q:
| q.execute('SELECT DATABASE() db,NOW() now');s['state']=q.fetchone();assert s['state']['db']=='mom-hm'
| q.execute("SELECT id,parent_id,name,path,component,permission FROM system_menu WHERE deleted=0 AND (name LIKE '%%生产%%' OR name LIKE '%%工艺%%' OR name LIKE '%%工序%%' OR name LIKE '%%报工%%' OR name LIKE '%%领料%%' OR path LIKE '%%pro%%') ORDER BY id");s['menus']=q.fetchall()
| q.execute('SELECT table_name,column_name,column_comment FROM information_schema.columns WHERE table_schema=DATABASE() ORDER BY table_name,ordinal_position');s['columns']=q.fetchall()
| tables=sorted({r['TABLE_NAME'] for r in s['columns']})
| for t in tables:
| if not (t.startswith(('mes_pro_','mes_pd_','crm_sale_quotation','crm_contract','erp_sale_order')) or t in ['mes_wm_issue_header','mes_wm_issue_line','mes_wm_return_header','mes_wm_return_line']):continue
| q.execute('SHOW CREATE TABLE `'+t+'`');schema=q.fetchone()
| fields={r['COLUMN_NAME'] for r in s['columns'] if r['TABLE_NAME']==t}
| q.execute('SELECT * FROM `'+t+'`'+(' WHERE deleted=0' if 'deleted' in fields else '')+' ORDER BY id');rows=q.fetchall()
| s['tables'][t]={'schema':schema,'fields':sorted(fields),'rows':rows}
| print(t,len(rows))
| refs=[r for r in s['columns'] if re.search(r'quotation|contract_id|sale_order|workorder|work_order|mps_id|task_id',r['COLUMN_NAME'])]
| s['references']=refs
| external={r['TABLE_NAME'] for r in refs if r['TABLE_NAME'] not in s['tables']}
| s['external_counts']={}
| for t in sorted(external):
| fields={r['COLUMN_NAME'] for r in s['columns'] if r['TABLE_NAME']==t}
| q.execute('SELECT COUNT(*) n FROM `'+t+'`'+(' WHERE deleted=0' if 'deleted' in fields else ''))
| s['external_counts'][t]=q.fetchone()['n']
| (P/'before-sales-production-delete.json').write_text(json.dumps(s,ensure_ascii=False,default=str,indent=2),encoding='utf8')
| print('menus',json.dumps(s['menus'],ensure_ascii=False));print('references',json.dumps(refs,ensure_ascii=False));print('external_counts',json.dumps(s['external_counts'],ensure_ascii=False))
| c.close()
|
|
