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
31
32
| import sys,json
| from pathlib import Path
| P=Path(__file__).parent
| sys.stdout.reconfigure(encoding='utf8')
| sys.path.insert(0,str(P.parent/'iqc-import-20261012/deps'))
| import pymysql
| def encode(x):return json.dumps(x,ensure_ascii=False,default=str,sort_keys=True)
| def connect(password):return pymysql.connect(host='36.213.150.237',port=10000,user='root',password=password,database='mom-hm',charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor,connect_timeout=15,read_timeout=120,autocommit=False)
| if __name__=='__main__':
| s={'tables':{}};c=connect(sys.stdin.readline().strip())
| with c.cursor() as q:
| q.execute("SET time_zone='+08:00'")
| q.execute('SELECT DATABASE() db,NOW() now');s['state']=q.fetchone()
| q.execute("SELECT DISTINCT TABLE_NAME FROM information_schema.columns WHERE TABLE_SCHEMA=DATABASE() AND COLUMN_NAME IN ('client_id','customer_id') AND (TABLE_NAME LIKE 'mes_%' OR TABLE_NAME LIKE 'crm_%' OR TABLE_NAME LIKE 'erp_%' OR TABLE_NAME LIKE 'mdm_%') ORDER BY TABLE_NAME")
| tables=[r['TABLE_NAME'] for r in q.fetchall()]
| tables=list(dict.fromkeys(['crm_customer']+tables+['mes_pro_feedback','mes_wm_product_produce','mes_wm_product_issue','mes_qc_ipqc','erp_sale_order_items','mes_wm_product_sales_line','mes_wm_product_produce_line','mes_md_item']))
| q.execute('SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA=DATABASE()');available={r['TABLE_NAME'] for r in q.fetchall()}
| for t in tables:
| if t not in available:continue
| q.execute('SHOW COLUMNS FROM `'+t+'`');columns=q.fetchall();names={x['Field'] for x in columns}
| if not {'id','deleted'}<=names:continue
| q.execute('SELECT * FROM `'+t+'` WHERE deleted=0 ORDER BY id');rows=q.fetchall()
| s['tables'][t]={'columns':columns,'rows':rows}
| customers={r['id']:r for r in s['tables']['crm_customer']['rows']}
| for t,v in s['tables'].items():
| fields={x['Field'] for x in v['columns']} & {'client_id','customer_id'}
| for f in fields:
| invalid=[r for r in v['rows'] if r[f] not in customers]
| print(t,f,'总数',len(v['rows']),'客户空或失效',len(invalid),'引用ID',sorted({str(r[f]) for r in invalid})[:15])
| if invalid:print(' 缺项示例',encode([{k:r[k] for k in r if k in ['id','code','no','name',f,'sale_order_id','order_source_type','order_source_code','work_order_id','task_id','notice_id','source_doc_type','source_doc_id','source_doc_code','contract_id','business_id','parent_id']} for r in invalid[:5]]))
| P.mkdir(exist_ok=True);(P/'snapshot.json').write_text(encode(s),encoding='utf8');print('STATE',encode(s['state']))
| c.close()
|
|
