AiDOPWarehouse/doc/db/_tmp_check_s2.py

import pymysql

DB = dict(
    host="123.60.180.165",
    port=3306,
    user="aidopremote",
    password="1234567890aiDOP#",
    database="aidopdev",
    charset="utf8mb4",
)
C = "utf8mb4_0900_ai_ci"
tid = 797403760988229
inner_where = "IFNULL(a.Status,'') <> '' AND a.tenant_id = %s"
sql = f"""
SELECT
    a.RecID AS Id,
    a.Priority AS Priority,
    a.WorkOrd AS WorkOrd,
    a.LotSerial AS LotSerial,
    a.IssueSite AS IssueSite,
    a.ItemNum AS ItemNum,
    b.Descr AS Descr,
    b.Descr1 AS Descr1,
    a.QtyOrded AS QtyOrded,
    (IFNULL(a.LocationStock, 0) + IFNULL(a.OpQtyCompleted, 0)) AS LocationStock,
    a.QtyCompleted AS QtyCompleted,
    s.PlanDate AS PlanDate,
    s.ProdDate AS ProdDate,
    LOWER(a.Status) AS Status,
    COALESCE(NULLIF(TRIM(a.`Domain`), ''), IFNULL(CAST(a.tenant_id AS CHAR), '')) AS Domain,
    a.Urgent AS Urgent
FROM WorkOrdMaster a
LEFT JOIN ItemMaster b ON a.ItemNum = b.ItemNum 
LEFT JOIN ReplenishmentWeekPlan r
    ON a.WorkOrd = r.ProductionOrder
    AND CAST(a.`Domain` AS CHAR(64))  = CAST(r.factory_id AS CHAR(64)) 
LEFT JOIN crm_seorder se ON se.bill_no = a.SalesJob 
LEFT JOIN CustMaster cm ON cm.Cust = se.custom_no 
LEFT JOIN (
    SELECT `Domain`, WorkOrds, MIN(PlanDate) AS PlanDate, MIN(ProdDate) AS ProdDate
    FROM PeriodSequenceDet
    GROUP BY `Domain`, WorkOrds
) s ON CAST(a.`Domain` AS CHAR(64))  = CAST(s.`Domain` AS CHAR(64)) 
    AND a.WorkOrd  = s.WorkOrds 
LEFT JOIN (
    SELECT morder_no, MAX(create_time) AS checktime
    FROM b_examine_result
    GROUP BY morder_no
) exm ON exm.morder_no = a.WorkOrd 
LEFT JOIN WorkOrdInStorage ins
    ON a.WorkOrd  = ins.WorkOrd 
    AND ins.Remark  = '工单预留' 
WHERE {inner_where}
LIMIT 5
"""
conn = pymysql.connect(**DB)
c = conn.cursor()
try:
    c.execute(sql, (tid,))
    rows = c.fetchall()
    print("rows", len(rows), rows[:2])
except Exception as e:
    print("ERROR", e)
for bad_tid in [0, 1300000000001]:
    c.execute(f"SELECT COUNT(*) FROM ({sql.replace('LIMIT 5','')}) t", (bad_tid,))
    print("count tenant", bad_tid, c.fetchone()[0])
conn.close()