| 1234567891011121314151617181920212223242526272829303132333435363738 |
- #!/usr/bin/env python3
- import json, pymysql
- T=797403760988229
- conn=pymysql.connect(host='123.60.180.165',port=3306,user='aidopremote',password='1234567890aiDOP#',database='aidopdev',charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor)
- out={}
- with conn.cursor() as c:
- c.execute("SELECT COUNT(*) c FROM srm_purchase WHERE tenant_id=%s AND IFNULL(IsDeleted,0)=0", (T,))
- out['srm_purchase']=c.fetchone()['c']
- c.execute("SELECT number,supplier_number,quota_rate,is_active FROM srm_purchase WHERE tenant_id=%s LIMIT 5", (T,))
- out['purchase_sample']=c.fetchall()
- c.execute("""
- SELECT d.ItemNum, m.PurOrd, m.Buyer, m.USAGE,
- IFNULL(d.QtyOrded,0)-IFNULL(d.RctQty,0) open_qty
- FROM PurOrdDetail d
- JOIN PurOrdMaster m ON m.RecID=d.PurOrdRecID
- WHERE m.tenant_id=%s AND IFNULL(m.Status,'')<>'C'
- AND (IFNULL(d.QtyOrded,0)-IFNULL(d.RctQty,0))>0
- LIMIT 8""", (T,))
- out['po_lines']=c.fetchall()
- c.execute("SELECT ItemNum, PurMfg FROM ItemMaster WHERE tenant_id=%s AND PurMfg='P' LIMIT 5", (T,))
- out['pur_items']=c.fetchall()
- for it in ['3152C0064','112AB6752','1A00D33','PO-UAT-20260604-01']:
- c.execute('SELECT number,quota_rate,is_active,supplier_number FROM srm_purchase WHERE tenant_id=%s AND number=%s LIMIT 2',(T,it))
- out[f'sp_{it}']=c.fetchall()
- c.execute('SELECT ItemNum,PurMfg FROM ItemMaster WHERE ItemNum=%s LIMIT 1',(it,))
- out[f'im_{it}']=c.fetchone()
- c.execute("""
- SELECT sp.number AS itemnum, COUNT(*) po_cnt
- FROM srm_purchase sp
- INNER JOIN PurOrdDetail d ON d.ItemNum = sp.number
- INNER JOIN PurOrdMaster m ON m.RecID=d.PurOrdRecID AND m.tenant_id=%s
- WHERE sp.tenant_id=%s AND IFNULL(sp.quota_rate,0)>0 AND IFNULL(sp.IsDeleted,0)=0
- AND IFNULL(m.Status,'')<>'C' AND (IFNULL(d.QtyOrded,0)-IFNULL(d.RctQty,0))>0
- GROUP BY sp.number ORDER BY po_cnt DESC LIMIT 10
- """, (T,T))
- out['intersection']=c.fetchall()
- conn.close()
- print(json.dumps(out, ensure_ascii=False, indent=2, default=str))
|
