"""Clear remarks on the 23 shipment notice/outbound headers at user request."""
|
import sys,json
|
from pathlib import Path
|
P=Path(__file__).parent
|
sys.path.insert(0,str(P.parent/'iqc-import-20261012/deps'))
|
import pymysql
|
r=json.loads((P/'shipping-complete-result.json').read_text(encoding='utf8'))
|
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)
|
backup={};changed={}
|
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
|
for table,key in [('mes_wm_sales_notice','notice_id'),('mes_wm_product_sales','sales_id')]:
|
ids=[x[key] for x in r['orders']];assert len(ids)==23 and len(set(ids))==23
|
marks=','.join(['%s']*len(ids))
|
q.execute('SELECT * FROM '+table+' WHERE deleted=0 AND id IN ('+marks+') ORDER BY id FOR UPDATE',ids);rows=q.fetchall();assert len(rows)==23
|
expected={x[key]:x for x in r['orders']}
|
for row in rows:assert row['sale_order_id']==expected[row['id']]['order_id'] and row['status']==4
|
backup[table]=rows
|
path=P/('shipping-remarks-before-'+state['now'].strftime('%Y%m%d-%H%M%S')+'.json')
|
path.write_text(json.dumps({'state':state,'tables':backup},ensure_ascii=False,default=str,indent=2),encoding='utf8')
|
for table,rows in backup.items():
|
ids=[x['id'] for x in rows];marks=','.join(['%s']*len(ids))
|
q.execute('UPDATE '+table+' SET remark=NULL,updater=%s,update_time=%s WHERE deleted=0 AND id IN ('+marks+')',['244',state['now'],*ids]);changed[table]=q.rowcount
|
q.execute('SELECT * FROM '+table+' WHERE deleted=0 AND id IN ('+marks+') ORDER BY id',ids);after=q.fetchall()
|
for old,new in zip(rows,after):
|
assert old['id']==new['id'] and new['remark'] is None
|
assert {k for k in old if old[k]!=new[k]}<={'remark','updater','update_time'}
|
c.commit()
|
# Read again after commit, independently of uncommitted writes.
|
for table,rows in backup.items():
|
ids=[x['id'] for x in rows]
|
q.execute('SELECT id,remark FROM '+table+' WHERE deleted=0 AND id IN ('+','.join(['%s']*len(ids))+')',ids)
|
after=q.fetchall();assert len(after)==23 and all(x['remark'] is None for x in after)
|
result={'committed':True,'verified':True,'at':str(state['now']),'cleared':changed,'backup':path.name,'scope':'发货通知及销售出库单表头备注；明细及其他字段不变'}
|
(P/'shipping-remarks-cleared-result.json').write_text(json.dumps(result,ensure_ascii=False,indent=2),encoding='utf8');print(json.dumps(result,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()
|
