wms / wms4_globalnancangkuangji · Files

import pyodbc

conn = pyodbc.connect(
    'DRIVER={ODBC Driver 17 for SQL Server};'
    'SERVER=192.168.1.168,1433;'
    'DATABASE=wms4_global;'
    'UID=sa;PWD=HHrobot123.'
)

cursor = conn.cursor()

print("=" * 90)
print("【生产环境】ERP推送问题单据完整分析")
print("=" * 90)

# ========== 一、入库单分析 ==========
print("\n\n【一、入库单问题分析】")
print("-" * 70)

# 状态300的入库单
cursor.execute("""
    SELECT COUNT(*) FROM receipt_header 
    WHERE first_status = 300 
    AND type NOT IN ('DBR', 'dbr')
""")
receipt_300 = cursor.fetchone()[0]
print(f"入库单状态300的数量: {receipt_300}")

print("\n入库单状态300详情:")
print(f"{'入库单号':<35} {'类型':<12} {'头状态':<8} {'尾状态':<8} {'组盘码':<25}")
print("-" * 88)

cursor.execute("""
    SELECT 
        rh.code,
        rh.type,
        rh.first_status,
        rh.last_status,
        rch.container_code,
        rch.status as container_status,
        th.status as task_status
    FROM receipt_header rh
    LEFT JOIN receipt_container_header rch ON rh.id = rch.id
    LEFT JOIN task_header th ON rch.id = th.receipt_container_header_id
    WHERE rh.first_status = 300
    AND rh.type NOT IN ('DBR', 'dbr')
    ORDER BY rh.create_time DESC
""")

receipt_problems = cursor.fetchall()
for row in receipt_problems:
    code = row[0] or ''
    bill_type = row[1] or ''
    first = row[2] if row[2] is not None else '-'
    last = row[3] if row[3] is not None else '-'
    container = row[4] or '无组盘'
    print(f"{code:<35} {bill_type:<12} {str(first):<8} {str(last):<8} {str(container):<25}")

# ========== 二、出库单分析 ==========
print("\n\n【二、出库单问题分析】")
print("-" * 70)

cursor.execute("""
    SELECT COUNT(*) FROM shipment_header 
    WHERE first_status = 300 
    AND type NOT IN ('DBR', 'dbr')
""")
shipment_300 = cursor.fetchone()[0]
print(f"出库单状态300的数量: {shipment_300}")

# 按类型统计
print("\n按类型统计:")
cursor.execute("""
    SELECT 
        type,
        COUNT(*) as cnt
    FROM shipment_header
    WHERE first_status = 300
    AND type NOT IN ('DBR', 'dbr')
    GROUP BY type
    ORDER BY cnt DESC
""")
for row in cursor.fetchall():
    print(f"  {row[0]}: {row[1]} 条")

print("\n出库单状态300详情（通过container_code关联）:")
print(f"{'出库单号':<35} {'类型':<12} {'组盘码':<25} {'组盘状态':<10} {'任务状态':<10}")
print("-" * 92)

cursor.execute("""
    SELECT 
        sh.code,
        sh.type,
        sch.container_code,
        sch.status as container_status,
        th.status as task_status
    FROM shipment_header sh
    LEFT JOIN shipment_container_header sch ON sh.id = sch.id
    LEFT JOIN task_header th ON sch.id = th.shipment_container_header_id
    WHERE sh.first_status = 300
    AND sh.type NOT IN ('DBR', 'dbr')
    ORDER BY sh.create_time DESC
""")

shipment_problems = cursor.fetchall()
for row in shipment_problems:
    code = row[0] or ''
    bill_type = row[1] or ''
    container = row[2] or '无组盘'
    container_status = row[3] if row[3] is not None else '-'
    task_status = row[4] if row[4] is not None else '-'
    print(f"{code:<35} {bill_type:<12} {str(container):<25} {str(container_status):<10} {str(task_status):<10}")

# ========== 三、任务状态分析 ==========
print("\n\n【三、任务状态分析（重点关注状态不是50的）】")
print("-" * 70)

# 统计任务状态分布
print("任务状态分布:")
cursor.execute("""
    SELECT 
        status,
        COUNT(*) as cnt
    FROM task_header
    GROUP BY status
    ORDER BY status
""")
for row in cursor.fetchall():
    print(f"  任务状态 {row[0]}: {row[1]} 条")

# ========== 四、组盘头表状态分析 ==========
print("\n\n【四、组盘头表状态分析】")
print("-" * 70)

print("receipt_container_header.status 分布:")
cursor.execute("""
    SELECT 
        status,
        COUNT(*) as cnt
    FROM receipt_container_header
    GROUP BY status
    ORDER BY status
""")
for row in cursor.fetchall():
    print(f"  状态 {row[0]}: {row[1]} 条")

print("\nshipment_container_header.status 分布:")
cursor.execute("""
    SELECT 
        status,
        COUNT(*) as cnt
    FROM shipment_container_header
    GROUP BY status
    ORDER BY status
""")
for row in cursor.fetchall():
    print(f"  状态 {row[0]}: {row[1]} 条")

# ========== 五、问题总结 ==========
print("\n\n【五、问题总结】")
print("=" * 70)

# 入库单
cursor.execute("""
    SELECT COUNT(*) FROM receipt_header 
    WHERE first_status = 300 
    AND type NOT IN ('DBR', 'dbr')
""")
total_receipt = cursor.fetchone()[0]

# 出库单
cursor.execute("""
    SELECT COUNT(*) FROM shipment_header 
    WHERE first_status = 300 
    AND type NOT IN ('DBR', 'dbr')
""")
total_shipment = cursor.fetchone()[0]

print(f"需要处理的入库单（状态300，排除调拨入库）: {total_receipt} 条")
print(f"需要处理的出库单（状态300，排除调拨出库）: {total_shipment} 条")
print(f"总计: {total_receipt + total_shipment} 条")

print("\n状态含义说明:")
print("  入库单 first_status:")
print("    0 = 新建")
print("    100 = 已提交")
print("    200 = 已确认")
print("    300 = 组盘中/拣货中")
print("    800 = 已回传ERP")
print("    900 = 已完成")
print()
print("  任务 status:")
print("    1 = 未开始")
print("    50 = 进行中")
print("    100 = 已完成")

conn.close()
print("\n\n分析完成！")