#!/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))