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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
| """Set document/line creation dates to actual business dates as user requested.
| User explicitly excluded planned receivables and expected opportunity closing dates.
| """
| import sys,json,csv
| 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
| canon=lambda x:json.loads(json.dumps(x,ensure_ascii=False,default=str))
| headers={'crm_sale_quotation':'quotation_time','crm_contract':'order_date','erp_sale_order':'order_time','crm_invoice':'invoice_time','crm_receivable':'return_time'}
| children={'crm_sale_quotation_product':('crm_sale_quotation','quotation_id'),'crm_contract_product':('crm_contract','contract_id'),'erp_sale_order_items':('erp_sale_order','order_id')}
| protected=['crm_receivable_plan','crm_business','crm_customer','crm_clue','crm_contact','crm_follow_up_record','crm_permission']
| c=pymysql.connect(host='36.213.150.237',port=10000,user='root',password=sys.stdin.readline().strip(),database='mom-hm',charset='utf8mb4',autocommit=False,cursorclass=pymysql.cursors.DictCursor)
| try:
| with c.cursor() as q:
| q.execute('SELECT DATABASE() db,NOW() now,GET_LOCK(%s,10) locked',('sales-ledger-202609',));state=q.fetchone();assert state['db']=='mom-hm' and state['locked']==1
| before={};data={}
| for table in [*headers,*children,*protected]:
| q.execute('SELECT * FROM '+table+' WHERE deleted=0 ORDER BY id'+(' FOR UPDATE' if table not in protected else ''));rows=q.fetchall();before[table]=rows;data[table]={x['id']:x for x in rows}
| backup=P/('business-create-times-before-'+state['now'].strftime('%Y%m%d-%H%M%S')+'.json')
| backup.write_text(json.dumps({'state':state,'tables':before},ensure_ascii=False,default=str,indent=2),encoding='utf8')
| targets={};missing=[];changes=[];counts={}
| for table,field in headers.items():
| for x in before[table]:
| date=x[field]
| if date is None:missing.append({'table':table,'id':x['id'],'reason':'无明确业务日期，保留创建时间'});continue
| assert date<=state['now'],('Actual date in future',table,x['id'])
| targets[(table,x['id'])]=(date,field)
| for table,(parent,fk) in children.items():
| for x in before[table]:
| if (parent,x[fk]) in targets:targets[(table,x['id'])]=(targets[(parent,x[fk])][0],parent+'.'+headers[parent])
| else:missing.append({'table':table,'id':x['id'],'reason':'父单无有效业务日期，保留创建时间'})
| for (table,id),(date,basis) in targets.items():
| x=data[table][id]
| if x['create_time']==date:continue
| q.execute('UPDATE '+table+' SET create_time=%s,updater=%s,update_time=%s WHERE id=%s AND deleted=0',(date,'291',state['now'],id));assert q.rowcount==1
| changes.append({'table':table,'id':id,'old_create_time':str(x['create_time']),'create_time':str(date),'basis':basis});counts[table]=counts.get(table,0)+1
| expected_changed={(x['table'],x['id']) for x in changes};after={}
| for table,rows in before.items():
| q.execute('SELECT * FROM '+table+' WHERE deleted=0 ORDER BY id');newrows=q.fetchall();after[table]=newrows;assert len(rows)==len(newrows)
| for old,new in zip(rows,newrows):
| assert old['id']==new['id'];diff={k for k in old if old[k]!=new[k]}
| assert diff<=({'create_time','updater','update_time'} if (table,old['id']) in expected_changed else set()),(table,old['id'],diff)
| if (table,old['id']) in targets:assert new['create_time']==targets[(table,old['id'])][0]
| c.commit()
| for table,expected in after.items():
| q.execute('SELECT * FROM '+table+' WHERE deleted=0 ORDER BY id');assert q.fetchall()==expected,('Post-commit mismatch',table)
| result={'committed':True,'verified':True,'at':str(state['now']),'counts':counts,'headers_changed':sum(counts.get(t,0) for t in headers),'details_changed':sum(counts.get(t,0) for t in children),'changes':changes,'missing_preserved':missing,'excluded':'用户确认：回款计划和商机预计日期不改；客户/线索/跟进的预约时间不作创建日期','backup':backup.name,'other_business_fields_unchanged':True}
| (P/'business-create-times-result.json').write_text(json.dumps(result,ensure_ascii=False,indent=2),encoding='utf8')
| with (P/'营销业务创建时间同步清单.csv').open('w',encoding='utf-8-sig',newline='') as f:
| w=csv.writer(f);w.writerow(['业务表','记录ID','原创建时间','新创建时间','依据'])
| for x in changes:w.writerow([x['table'],x['id'],x['old_create_time'],x['create_time'],x['basis']])
| print(json.dumps({k:v for k,v in result.items() if k!='changes'},ensure_ascii=False))
| except Exception:c.rollback();raise
| finally:
| with c.cursor() as q:q.execute('SELECT RELEASE_LOCK(%s)',('sales-ledger-202609',))
| c.close()
|
|
