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
59
60
| """Prepare native notice -> outbound drafts; do not post stock or claim goods shipped."""
| import sys,json,csv
| from pathlib import Path
| from decimal import Decimal
| P=Path(__file__).parent
| sys.path.insert(0,str(P.parent/'iqc-import-20261012/deps'))
| import pymysql
| source=json.loads((P/'source-order-regroup-result.json').read_text(encoding='utf8'))
| assert not (P/'shipping-drafts-result.json').exists(),'Drafts already generated; do not duplicate.'
| 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)
| D=Decimal
| result={'orders':[],'excluded':[],'stock_posted':False,'workflow':'发货通知提交待出库 -> 出库草稿；等待历史/实际扣库口径确认；未拣货、未填写运单、未执行库存事务'}
| try:
| with c.cursor() as q:
| q.execute('SELECT NOW() now,DATABASE() db,GET_LOCK(%s,10) locked',('sales-ledger-202609',));state=q.fetchone();assert state['db']=='mom-hm' and state['locked']==1;now=state['now']
| q.execute('SELECT id,status FROM system_users WHERE id=244 AND deleted=0');assert q.fetchone()['status']==0
| tabs=['erp_sale_order','erp_sale_order_items','mes_wm_sales_notice','mes_wm_sales_notice_line','mes_wm_product_sales','mes_wm_product_sales_line','mes_wm_product_sales_detail','mes_wm_stock_reserve','mes_wm_transaction','mes_wm_material_stock','mes_wm_batch','mdm_item','mes_wm_warehouse','mes_wm_warehouse_location','mes_wm_warehouse_area','mes_qc_oqc']
| backup={'state':state,'tables':{}};data={};schemas={}
| for t in tabs:
| q.execute('SELECT * FROM `'+t+'` WHERE deleted=0 ORDER BY id');rs=q.fetchall();backup['tables'][t]=rs;data[t]={r['id']:r for r in rs}
| q.execute('SHOW COLUMNS FROM `'+t+'`');schemas[t]={r['Field']:r for r in q.fetchall()}
| (P/'shipping-drafts-locked-before.json').write_text(json.dumps(backup,ensure_ascii=False,default=str,indent=2),encoding='utf8')
| def insert(t,v):
| v={**v,'creator':'244','updater':'244','create_time':now,'update_time':now,'deleted':0}
| assert all(k in schemas[t] for k in v)
| q.execute('INSERT INTO `'+t+'` ('+','.join('`'+k+'`' for k in v)+') VALUES ('+','.join(['%s']*len(v))+')',list(v.values()));return q.lastrowid
| for entry in source['orders']:
| order=data['erp_sale_order'][entry['order_id']];no=order['no'];lines=[x for x in data['erp_sale_order_items'].values() if x['order_id']==order['id']]
| assert no==entry['source_no'] and order['out_count']==0 and all(x['out_count']==0 for x in lines)
| if order['total_count']<=0:result['excluded'].append({'no':no,'reason':'退货负数量单，不生成正向发货'});continue
| assert all(x['count']>0 for x in lines) and order['status']==20
| assert not any(x['sale_order_id']==order['id'] for x in data['mes_wm_sales_notice'].values()),('Notice exists',no)
| assert not any(x['sale_order_id']==order['id'] for x in data['mes_wm_product_sales'].values()),('Outbound exists',no)
| q.execute('SELECT * FROM crm_customer WHERE id=%s AND deleted=0',(order['customer_id'],));customer=q.fetchone();assert customer
| code='FHTZ-202609-'+no;outcode='XSCK-202609-'+no
| note=f'九月销售原单{no}发货流程补录；库存为9月底结存，历史补录/实际扣库口径待确认；暂无承运商、运单和真实生产批次；尚未扣库。'
| notice=insert('mes_wm_sales_notice',{'code':code,'name':'销售订单['+no+']发货通知','client_id':customer['id'],'sale_order_id':order['id'],'sale_order_code':no,'sales_date':now,'recipient_telephone':customer['telephone'] or customer['mobile'],'recipient_address':customer['detail_address'],'status':3,'out_status':0,'remark':note})
| sales=insert('mes_wm_product_sales',{'code':outcode,'name':'销售订单['+no+']出库草稿','client_id':customer['id'],'sale_order_id':order['id'],'notice_id':notice,'sales_order_code':no,'sales_date':now,'contact_telephone':customer['telephone'] or customer['mobile'],'contact_address':customer['detail_address'],'status':0,'remark':note})
| o={'no':no,'order_id':order['id'],'customer_id':customer['id'],'customer':customer['name'],'notice_id':notice,'notice_code':code,'sales_id':sales,'sales_code':outcode,'lines':[]}
| for l in lines:
| item=data['mdm_item'][l['product_id']];assert item['status']==0
| unitmatch=l['product_unit_id']==item['unit_measure_id']
| ln=f'原订单明细{l["id"]}；销售单位ID{l["product_unit_id"]}；物料主单位ID{item["unit_measure_id"]}；'+('单位一致。' if unitmatch else '单位换算待确认，草稿数量按原销售数保留，禁止据此扣库。')
| nl=insert('mes_wm_sales_notice_line',{'notice_id':notice,'sale_order_item_id':l['id'],'item_id':item['id'],'quantity':l['count'],'out_count':0,'oqc_check_flag':0,'remark':ln})
| sl=insert('mes_wm_product_sales_line',{'sales_id':sales,'sale_order_item_id':l['id'],'notice_line_id':nl,'item_id':item['id'],'enter_unit_measure_id':l['product_unit_id'],'enter_quantity':l['count'],'quantity':l['count'],'oqc_check_flag':0,'quality_status':None,'remark':ln})
| o['lines'].append({'order_item_id':l['id'],'notice_line_id':nl,'sales_line_id':sl,'item_id':item['id'],'quantity':str(l['count']),'sale_unit_id':l['product_unit_id'],'primary_unit_id':item['unit_measure_id'],'unit_match':unitmatch})
| # Native submitSalesNotice sets RESERVED=1 while no physical stock reservation has yet occurred.
| q.execute('UPDATE erp_sale_order SET out_status=1,updater=%s,update_time=%s WHERE id=%s',('244',now,order['id']))
| result['orders'].append(o)
| protected=['erp_sale_order_items','mes_wm_product_sales_detail','mes_wm_stock_reserve','mes_wm_transaction','mes_wm_material_stock','mes_wm_batch','mdm_item','mes_wm_warehouse','mes_wm_warehouse_location','mes_wm_warehouse_area','mes_qc_oqc']
| for t in protected:
| q.execute('SELECT * FROM `'+t+'` WHERE deleted=0 ORDER BY id');assert q.fetchall()==backup['tables'][t],('Unintended stock/quality changes',t)
| assert len(result['orders'])==23 and sum(len(o['lines']) for o in result['orders'])==75
| c.commit();result.update(committed=True,at=str(now),counts={'notices':23,'outbound_drafts':23,'lines':75,'unit_mismatch_lines':sum(not l['unit_match'] for o in result['orders'] for l in o['lines'])})
| (P/'shipping-drafts-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!='orders'},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()
|
|
