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("=" * 80)
print("【生产环境】问题单据详细分析 - 关联任务和组盘状态")
print("=" * 80)

# 1. 问题入库单详细分析
print("\n\n【一、入库单 apmt5802026040200040 详细分析】")
print("-" * 60)

# 查找入库单关联的组盘和任务
cursor.execute("""
    SELECT 
        rh.code,
        rh.type,
        rh.first_status,
        rh.last_status,
        rch.id as container_header_id,
        rch.container_code,
        rch.status as container_header_status,
        th.id as task_id,
        th.status as task_status,
        th.task_type
    FROM receipt_header rh
    LEFT JOIN receipt_container_header rch ON rh.id = rch.receipt_id
    LEFT JOIN task_header th ON rch.id = th.receipt_container_header_id
    WHERE rh.code = 'apmt5802026040200040'
""")

row = cursor.fetchone()
if row:
    print(f"入库单: {row[0]}")
    print(f"类型: {row[1]}")
    print(f"头状态: {row[2]}, 尾状态: {row[3]}")
    print(f"组盘头ID: {row[4]}, 组盘码: {row[5]}, 组盘状态: {row[6]}")
    print(f"任务ID: {row[7]}, 任务状态: {row[8]}, 任务类型: {row[9]}")
else:
    print("未找到该入库单")

# 2. 问题出库单详细分析
print("\n\n【二、出库单问题分析（关联组盘和任务）】")
print("-" * 60)

print(f"{'出库单号':<30} {'类型':<10} {'状态':<8} {'组盘码':<25} {'组盘状态':<10} {'任务状态':<10}")
print("-" * 93)

cursor.execute("""
    SELECT 
        sh.code,
        sh.type,
        sh.first_status,
        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.shipment_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
""")

problem_shipments = []
for row in cursor.fetchall():
    code = row[0] or ''
    bill_type = row[1] or ''
    status = row[2] if row[2] is not None else '-'
    container = (row[3] or '无组盘')[:20]
    container_status = row[4] if row[4] is not None else '-'
    task_status = row[5] if row[5] is not None else '-'
    problem_shipments.append({
        'code': code,
        'type': bill_type,
        'status': status,
        'container': container,
        'container_status': container_status,
        'task_status': task_status
    })
    print(f"{code:<30} {bill_type:<10} {str(status):<8} {container:<25} {str(container_status):<10} {str(task_status):<10}")

# 3. 按类型统计
print("\n\n【三、按类型统计问题单据】")
print("-" * 60)

cursor.execute("""
    SELECT 
        sh.type,
        COUNT(*) as total,
        SUM(CASE WHEN th.status IS NULL THEN 1 ELSE 0 END) as no_task,
        SUM(CASE WHEN th.status = 1 THEN 1 ELSE 0 END) as task_1,
        SUM(CASE WHEN th.status = 50 THEN 1 ELSE 0 END) as task_50,
        SUM(CASE WHEN th.status = 100 THEN 1 ELSE 0 END) as task_100
    FROM shipment_header sh
    LEFT JOIN shipment_container_header sch ON sh.id = sch.shipment_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')
    GROUP BY sh.type
    ORDER BY total DESC
""")

print(f"{'类型':<15} {'总数':<8} {'无任务':<10} {'任务1':<10} {'任务50':<10} {'任务100':<10}")
print("-" * 63)
for row in cursor.fetchall():
    print(f"{row[0]:<15} {row[1]:<8} {row[2]:<10} {row[3]:<10} {row[4]:<10} {row[5]:<10}")

# 4. 查看任务状态字典
print("\n\n【四、任务状态含义】")
print("-" * 60)
print("1 = 未开始")
print("50 = 进行中")
print("100 = 已完成")
print("0 = 未知")

# 5. 组盘头表 status 分析
print("\n\n【五、组盘头表 receipt_container_header 的 status 分析】")
print("-" * 60)

cursor.execute("""
    SELECT 
        status,
        COUNT(*) as cnt
    FROM receipt_container_header
    GROUP BY status
    ORDER BY status
""")

print("入库组盘头状态分布:")
for row in cursor.fetchall():
    print(f"  状态 {row[0]}: {row[1]} 条")

cursor.execute("""
    SELECT 
        status,
        COUNT(*) as cnt
    FROM shipment_container_header
    GROUP BY status
    ORDER BY status
""")

print("\n出库组盘头状态分布:")
for row in cursor.fetchall():
    print(f"  状态 {row[0]}: {row[1]} 条")

# 6. 总结
print("\n\n【六、问题总结】")
print("=" * 60)

# 统计任务状态不是50的
cursor.execute("""
    SELECT COUNT(*)
    FROM shipment_header sh
    LEFT JOIN shipment_container_header sch ON sh.id = sch.shipment_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')
    AND (th.status IS NULL OR th.status != 50)
""")
not_50_task = cursor.fetchone()[0]

cursor.execute("""
    SELECT COUNT(*)
    FROM shipment_header sh
    LEFT JOIN shipment_container_header sch ON sh.id = sch.shipment_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')
""")
total_300 = cursor.fetchone()[0]

print(f"状态300的出库单总数: {total_300}")
print(f"其中任务状态不是50的: {not_50_task}")

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