"""User-requested presentation cleanup of marketing remarks only. Original source/provenance remains in the backup; no transaction is asserted real. """ import sys,json,re from pathlib import Path from collections import defaultdict P=Path(__file__).parent sys.stdout.reconfigure(encoding='utf8') sys.path.insert(0,str(P.parent/'iqc-import-20261012/deps')) import pymysql assert not (P/'result.json').exists(),'Already applied; inspect the result before repeating.' demo='10月8日至14日业务链演示补充;非真实交易、投料、检验或物流凭证;沿用现有示意BOM及工艺主数据。' def clean(t,text): if not text:return text s=text.strip().replace(demo,'').strip() s=s.replace('约定回款日期与比例为演示计划;','') s=re.sub(r'^九月台账补录202609-L\d+,行\d+-\d+;原单[^。]+。含税合并金额[-\d.]+;净税额与合并金额尾差[-\d.]+元,按台账合并金额登记。','',s) s=re.sub(r'^九月台账202609-L\d+;','',s) if s.startswith('台账行') and t in ['crm_sale_quotation_product','erp_sale_order_items']: s=re.sub(r'^台账行\d+;原单[^;]+;原项目号[^;]+;原净额[-\d.]+,税[-\d.]+,含税[-\d.]+','',s) if t=='crm_customer': s=re.sub(r'^九月销售台账补录;来源客户号\d+;原备注:','',s) if s.startswith('由商机导入补建,来源:'):s='' if s=='来源:销售报价单_2026.8.1-10.11.xlsx;客户名称按明细表主体,不替换为门店':s='' if t=='crm_clue':s=re.sub(r'^来自销售收入台账渠道分类(([^)]+)),',r'渠道:\1;',s) s=s.replace('原票据未提供附件。','发票附件待补充。') s=s.replace('两张发票合并登记;只有合计金额,未分配单张金额,不代表单张发票。','两张发票合计登记,单张金额待核对。') s=s.replace('用户确认到账日期取开票日;无票电商取备注销售日。','') s=s.replace('暂无原票附件,按系统允许的未关联开票回款登记,合同已关联;到账方式保留原备注,未推断转账渠道。','已关联合同,暂未关联发票。') s=s.replace('原票号无需开票。','无需开票。').replace('原票号','发票号:') s=s.replace('日期按开票/销售日后30天补录测算,非约定账期;台账状态截至2026-09-29,实际到账另行核对。','计划日期待确认;实际到账情况待核对。') s=s.replace('只回了12600,剩余部分未回款','已回款12,600元,剩余5,400元待回款') s=s.replace('(青海南玻新能源科技有限公司0开','(青海南玻新能源科技有限公司)开') for old,new in [('开专票一张','开具增值税专用发票1张'),('开专票两张','开具增值税专用发票2张'),('开普票一张','开具普通发票1张'),('开普票两张','开具普通发票2张')]:s=s.replace(old,new) if s=='退货原单,负数量负金额保留':s='退货订单。' s=re.sub(r'[;;]{2,}',';',s).strip(' ;;。') return s or None 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) result={'counts':{},'changes':[]} try: with c.cursor() as q: q.execute("SET time_zone='+08:00'") q.execute('SELECT DATABASE() db,NOW() now,GET_LOCK(%s,10) locked',('marketing-remarks-20261014',));state=q.fetchone();assert state['db']=='mom-hm' and state['locked']==1 q.execute("SELECT table_name FROM information_schema.columns WHERE table_schema=DATABASE() AND column_name='remark' AND (table_name LIKE 'crm_%' OR table_name IN ('erp_sale_order','erp_sale_order_items')) ORDER BY table_name");tabs=[x['TABLE_NAME'] for x in q.fetchall()] before={} for t in tabs: q.execute('SHOW COLUMNS FROM '+t);cols={x['Field'] for x in q.fetchall()} if not {'id','deleted','updater','update_time'}<=cols:continue q.execute('SELECT * FROM '+t+' WHERE deleted=0 ORDER BY id FOR UPDATE');before[t]=q.fetchall() q.execute('SELECT status FROM system_users WHERE id=291 AND deleted=0');assert q.fetchone()['status']==0 backup=P/('locked-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') expected={} for t,rows in before.items(): grouped=defaultdict(list) for old in rows: new=clean(t,old['remark']) if old['remark']==new:continue assert not any(w in (new or '') for w in ['非真实交易','业务链演示','示意BOM','九月台账','台账补录','由商机导入补建']) change={'table':t,'id':old['id'],'old_remark':old['remark'],'remark':new};result['changes'].append(change);expected[(t,old['id'])]=new;grouped[new].append(old['id']) result['counts'][t]=sum(len(ids) for ids in grouped.values()) for new,ids in grouped.items(): q.execute('UPDATE '+t+' SET remark=%s,updater=%s,update_time=update_time WHERE deleted=0 AND id IN ('+','.join(['%s']*len(ids))+')',[new,'291',*ids]);assert q.rowcount==len(ids) after={} for t,rows in before.items(): q.execute('SELECT * FROM '+t+' WHERE deleted=0 ORDER BY id');newrows=q.fetchall();after[t]=newrows;assert len(rows)==len(newrows) for old,new in zip(rows,newrows): assert old['id']==new['id'];key=(t,old['id']);diff={k for k in old if old[k]!=new[k]};assert diff<=({'remark','updater'} if key in expected else set()),(t,old['id'],diff) if key in expected:assert new['remark']==expected[key] assert '非真实交易' not in (new['remark'] or '') c.commit() for t,rows in after.items(): q.execute('SELECT * FROM '+t+' WHERE deleted=0 ORDER BY id');assert q.fetchall()==rows,('Post-commit mismatch',t) result.update(committed=True,verified=True,at=str(state['now']),total=len(result['changes']),backup=backup.name,amounts_relations_statuses_dates_and_nonremark_data_unchanged=True) (P/'result.json').write_text(json.dumps(result,ensure_ascii=False,indent=2),encoding='utf8') 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)',('marketing-remarks-20261014',)) c.close()