"""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()
|
